 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertUpdate_FieldStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertUpdate_FieldStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsert_FieldStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsert_FieldStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_Name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_Name]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_ProviderName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_ProviderName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_ProviderNameRecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_ProviderNameRecord]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_ProviderOtherData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_ProviderOtherData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_ProviderReference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_ProviderReference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_Reference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_Reference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprDelete_SystemProviderRIS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprDelete_SystemProviderRIS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprGetClassifications]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprGetClassifications]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprGetNode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprGetNode]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertNameRecords]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertNameRecords]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_Provider]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_Provider]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_ProviderConcept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_ProviderConcept]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_ProviderImport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_ProviderImport]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_ProviderName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_ProviderName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_ProviderOtherData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_ProviderOtherData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_ProviderRIS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_ProviderRIS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_ProviderReference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_ProviderReference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsertUpdate_SystemProviderConcept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsertUpdate_SystemProviderConcept]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_Concept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_Concept]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_Name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_Name]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_ProviderConceptChange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_ProviderConceptChange]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_ProviderNameChange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_ProviderNameChange]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_ProviderRISChange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_ProviderRISChange]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_ProviderReferenceChange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_ProviderReferenceChange]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_Reference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_Reference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_ReferenceRIS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_ReferenceRIS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprInsert_ReportError]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprInsert_ReportError]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprLink_NameParent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprLink_NameParent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprLink_NamePreferred]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprLink_NamePreferred]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSearch_References]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSearch_References]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ConceptMatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ConceptMatches]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_FieldStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_FieldStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_FieldStatusIdentifiers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_FieldStatusIdentifiers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_FieldStatusLevels]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_FieldStatusLevels]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_FieldStatus_RecordByKeyAndTableName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_FieldStatus_RecordByKeyAndTableName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ImportTypes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ImportTypes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Literature_Search3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Literature_Search3]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Name]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NameConcepts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NameConcepts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NameMappings]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NameMappings]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NameMatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NameMatches]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NameOtherData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NameOtherData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithCanonical]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithCanonical]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithParent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithParent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithParitalAuthors]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithParitalAuthors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithPartialAuthors]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithPartialAuthors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithRank]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithRank]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithRankAndYear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithRankAndYear]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NamesWithYear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NamesWithYear]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NoNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NoNames]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_NodeToRoot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_NodeToRoot]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ParentProviderConcept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ParentProviderConcept]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ParentProviderName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ParentProviderName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Provider]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Provider]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderConcept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderConcept]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderConceptById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderConceptById]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderConcetps]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderConcetps]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderImports]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderImports]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderNameById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderNameById]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderNames]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderRIS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderRIS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderRISById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderRISById]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderRISByReference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderRISByReference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderRISByReferenceGuid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderRISByReferenceGuid]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderRISByReferenceId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderRISByReferenceId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderReference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderReference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderReferenceById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderReferenceById]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProviderReferences]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProviderReferences]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Providers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Providers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProvidersForName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProvidersForName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ProvidersForReference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ProvidersForReference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_RISByReference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_RISByReference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_RISMappings]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_RISMappings]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Ranks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Ranks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Reference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Reference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ReferenceMatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ReferenceMatches]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ReferencesWithCitation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ReferencesWithCitation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_RelationshipTypes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_RelationshipTypes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ReportConflictingData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ReportConflictingData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ReportLowMatchScores]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ReportLowMatchScores]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ReportMultipleMatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ReportMultipleMatches]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_ReportUnlinkedRecords]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_ReportUnlinkedRecords]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Reports]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Reports]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_RootKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_RootKey]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_RunReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_RunReport]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SearchNames2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SearchNames2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SearchProviderNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SearchProviderNames]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SearchProviderRefs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SearchProviderRefs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SystemProviderConcept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SystemProviderConcept]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SystemProviderConceptToName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SystemProviderConceptToName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SystemProviderNameForName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SystemProviderNameForName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_SystemProviderRefForRef]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_SystemProviderRefForRef]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_UknownTaxaNode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_UknownTaxaNode]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_UnlinkedConcepts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_UnlinkedConcepts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_UnlinkedNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_UnlinkedNames]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_UnlinkedReferences]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_UnlinkedReferences]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_UserProviders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_UserProviders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprSelect_Users]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprSelect_Users]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdateProviderNameLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdateProviderNameLink]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ConceptData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ConceptData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ConceptLinks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ConceptLinks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_LinkedProviderNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_LinkedProviderNames]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_LinkedProviderReferences]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_LinkedProviderReferences]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_Name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_Name]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_NameData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_NameData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_NameRank]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_NameRank]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_NameRelationships]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_NameRelationships]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ProviderConceptRelationship]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ProviderConceptRelationship]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ProviderConceptStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ProviderConceptStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ProviderNameConcepts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ProviderNameConcepts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ProviderNameRank]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ProviderNameRank]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ProviderNameRelationships]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ProviderNameRelationships]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_Reference]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_Reference]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprUpdate_ReferenceData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sprUpdate_ReferenceData]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure spInsertUpdate_FieldStatus
	@recordId nvarchar(300), 
	@fieldStatusId int,
	@statusLevelFk int,
	@user nvarchar(50)
AS
	
	if exists(select * from tblFieldStatus where FieldStatusIdentifierFk = @fieldStatusId and FieldStatusRecordFk = @recordId)
	begin
		update tblFieldStatus
		set FieldStatusLevelFk = @statusLevelFk,
			FieldStatusUpdatedBy = @user,
			FieldStatusUpdatedDate = getdate()
		where FieldStatusIdentifierFk = @fieldStatusId and FieldStatusRecordFk = @recordId
	end
	else
	begin
		insert tblFieldStatus(FieldStatusIdentifierFk, FieldStatusLevelFk, FieldStatusRecordFk, FieldStatusCreatedDate, FieldStatusCreatedBy)
		select @fieldStatusId, @statusLevelFk, @recordId, getdate(), @user
	end
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure spInsert_FieldStatus
	@recordId nvarchar(300),
	@tableName nvarchar(300),
	@fieldName nvarchar(300),
	@statusLevelFk int,
	@user nvarchar(50)
AS

	declare @statusId int
	select @statusId = FieldStatusIdentifierCounterPk 
	from tblFieldStatusIdentifier 
	where FieldStatusIdentifierTableName = @tableName and
		FieldStatusIdentifierFieldName = @fieldName
		
	delete tblFieldStatus where FieldStatusIdentifierFk = @statusId and FieldStatusRecordFk = @recordId
	
	insert tblFieldStatus(FieldStatusIdentifierFk, FieldStatusLevelFk, FieldStatusRecordFk, FieldStatusCreatedDate, FieldStatusCreatedBy)
	select @statusId, @statusLevelFk, @recordId, getdate(), @user


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_Name
	@nameLsid nvarchar(300),
	@newNameLsid nvarchar(300),
	@user nvarchar(50)
AS

	declare @nameGuid uniqueidentifier 
	select @nameGuid = NameGuid from tblName where NameLsid = @nameLsid

	insert into tblDeprecated 
	select @NameLSID, @newNameLsid, getdate(), @user
	
	delete tblName
	where NameLsid = @nameLsid


	delete tblFieldStatus where FieldStatusRecordFk = cast(@nameGuid as varchar(38))
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_ProviderName
	@PNPk int
AS

	delete tblProviderName
	where PNPk = @PNPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_ProviderNameRecord
	@providerPk int,
	@providerNameId nvarchar(4000) --original providers id, not the PNPk
AS

	delete pn
	from tblProviderName pn
	inner join tblProviderImport pim on pim.ProviderImportPk = pn.PNProviderImportFk
	where ProviderImportProviderFk = @providerPk and PNNameId = @providerNameId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_ProviderOtherData
	@providerPk int,
	@otherDataId nvarchar(300)
AS

	delete od
	from tblProviderOtherData od
	inner join tblProviderImport pim on pim.ProviderImportPk = od.POtherDataProviderImportFk
	where POtherDataRecordId = @otherDataId and pim.ProviderImportProviderFk = @providerPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_ProviderReference
	@providerPk int,
	@providerReferenceId nvarchar(4000) --original provider's ref id
	
AS

	delete pr
	from tblProviderReference pr
	inner join tblProviderImport pim on pim.ProviderImportPk = pr.PRProviderImportFk
	where ProviderImportProviderFk = @providerPk and PRReferenceId = @providerReferenceId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_Reference
	@refLsid nvarchar(300),
	@newRefLsid nvarchar(300),
	@user nvarchar(50)
AS

	declare @refGuid uniqueidentifier 
	select @refGuid = ReferenceGuid from tblReference where ReferenceLsid = @refLsid

	insert into tblDeprecated 
	select @refLSID, @newRefLsid, getdate(), @user
	
	delete tblReference
	where ReferenceLsid = @refLsid


	delete tblFieldStatus where FieldStatusRecordFk = cast(@refGuid as varchar(300))
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprDelete_SystemProviderRIS
	@providerReferenceFk int
AS

	delete ris
	from vwProviderRIS pr
	inner join tblProviderRIS ris on ris.PRISPk = pr.PRISPk
	where pr.PRISProviderReferenceFk = @providerReferenceFk and pr.ProviderIsEditor = 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprGetClassifications
	
AS

	--todo is this needed?
	select 1 as ClassificationCounterPk, 
		'Compositae' as ClassificationDescription


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE procedure dbo.sprGetNode

@NameGuid					uniqueidentifier,
@intClassificationKey		int = 0,
@intRoleKey					int,
@bitDoSuppress				bit = 0

AS
	SET NOCOUNT ON
	
	DECLARE @bitSuppressMatch	bit
	IF @bitDoSuppress = 0 OR @bitDoSuppress IS NULL 
		SET @bitSuppressMatch = 1
	ELSE
		SET @bitSuppressMatch = 0
	
	--IF @intClassificationKey IS NULL OR @intClassificationKey = 0
	--	BEGIN
	
	SELECT n1.NameCreatedBy as NameAddedBy,
		n1.NameCreatedDate as NameAddedDate,
		null as NameAggregate,
		null as NameAnamorphGenusFK,
		n1.NameAuthors, 
		0 as NameAutonym,
		cast(n1.NameBasedOnFK as varchar(38)) as NameBasedOnFk,
		cast(n1.NameBasionymFK as varchar(38)) as NameBasionymFk,
		null as NameBlockingFK,
		isnull(n1.NameCanonical, '[unnamed]') as NameCanonical,
		null as NameCheckStatus,
		null as NameClassificationFK,
		cast(n1.NamePreferredFk as varchar(38)) as NameCurrentFK,
		0 as NameDubium,
		isnull(n1.NameFull, '') as NameFull,
		null as NameHybridLink,
		isnull(n1.NameIllegitimate, 0) as NameIllegitimate,
		0 as NameInCitation,
		isnull(n1.NameInvalid, 0) as NameInvalid,
		0 as NameIsAnamorph,
		isnull(n1.NameMisapplied, 0) as NameMisapplied,
		'ICBN' as NameNomCode,
		0 as NameNovum,
		n1.NameOrthography as NameOrthographyVariant,
		null as NameOwner,
		null as NamePage,
		cast(n1.NameParentFk as varchar(38)) as NameParentFK,
		null as NamePrimaryOwnerFk,
		isnull(n1.NameProParte, 0) as NameProParte,
		cast(n1.NameReferenceFK as varchar(38)) as NameReferenceFk,
		null as NameSanctioningAuthor,
		null as NameSanctioningPage,
		null as NameSanctioningReferenceFK,
		0 as NameSuppress,
		null as NameTaxonomyReferenceFK,
		isnull(n1.NameRankFk, -1) as NameTaxonRankFk,
		null as NameTempRepKey,
		null as NameTypeLocality,
		cast(n1.NameTypeNameFk  as varchar(38))as NameTypeTaxonFK,
		n1.NameUpdatedBy,
		null as NameUpdatedByFK,
		n1.NameUpdatedDate,
		cast(n1.NameGuid as varchar(38)) as NameGuid,
		n1.NameYear as NameYearOfPublication, 
		null as NameYearOnPublication,  
		n1.NameParent as ParentName, 
		n1.NameBasionym as BasionymName, 
		null as BasionymDate,
		isnull(n1.NameLSID,'') as NameLSID,
		tr.RankName as TaxonRankName, tr.RankAbbreviation as TaxonRankAbbreviation,
		
		(
			SELECT count(*) FROM tblName b 
			WHERE b.NameParentFK = n1.NameGuid 
		) as ChildCount,
		--rn.NodeRolePk, 		
		--p1.*, 
		
		1 as PermissionRead,
		1 as PermissionWrite,
		1 as PermissionsAddChildren,
		1 as PermissionsChangeLinks,
		1 as PermissionsModifyPermission
		
		--p2.PermissionsPk as InheritedPermissionsPk,
		--p2.PermissionsRead as InheritedPermissionsRead,
		--p2.PermissionsWrite as InheritedPermissionsWrite,
		--p2.PermissionsAddChildren as InheritedPermissionsAddChildren,
		--p2.PermissionsChangeLinks as InheritedPermissionsChangeLinks,
		--p2.PermissionsModifyPermission as InheritedPermissionsModifyPermission,
		
		FROM tblName n1
	        left join tblName pn on pn.NameGuid = n1.NameParentFk	
			--LEFT JOIN tblNodeRole rn ON (rn.NodeRoleNodeFk = n1.NameGuid AND rn.NodeRoleRoleFk = @intRoleKey)
			--LEFT JOIN tblPermissions p1 ON p1.PermissionsPk = rn.NodeRolePermissionsFk
			--LEFT JOIN tblPermissions p2 ON p2.PermissionsPk = rn.NodeRoleInheritedPermissionsFk
			LEFT JOIN tblRank tr ON n1.NameRankFk = tr.RankPk
		WHERE n1.NameParentFK = @NameGuid
		--AND (n1.NameSuppress = 0 OR n1.NameSuppress = @bitSuppressMatch)
		
		ORDER BY tr.RankSort, n1.NameCanonical
		RETURN @@error
		--END
	--ELSE
	--	BEGIN
		
		--todo??
	--	END 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_Name
	@NameGUID uniqueidentifier, 
	@NameLSID nvarchar(300), 
	@NameFull nvarchar(300), 
	@NameRank nvarchar(50), 
	@NameRankFk int, 
	@NameParentFk uniqueidentifier,
	@NameParentName nvarchar(300),
	@NamePreferredFk uniqueidentifier,
	@NamePreferredName nvarchar(300),
	@NameCanonical nvarchar(300), 
	@NameAuthors nvarchar(300), 
	@NameBasionymAuthors nvarchar(300), 
	@NameCombinationAuthors nvarchar(300), 
	@NamePublishedIn nvarchar(1000), 
	@NameReferenceFk uniqueidentifier, 
	@NameYear nvarchar(20), 
	@NameMicroReference nvarchar(150), 
	@NameTypeVoucher ntext, 
	@NameTypeName nvarchar(300), 
	@NameTypeNameFk uniqueidentifier, 
	@NameOrthography nvarchar(300), 
	@NameBasionym nvarchar(300), 
	@NameBasionymFk uniqueidentifier, 
	@NameBasedOn nvarchar(300), 
	@NameBasedOnFk uniqueidentifier, 
	@NameConservedAgainst nvarchar(300), 
	@NameConservedAgainstFk uniqueidentifier, 
	@NameHomonymOf nvarchar(300), 
	@NameHomonymOfFk uniqueidentifier, 
	@NameReplacementFor nvarchar(300), 
	@NameReplacementForFk uniqueidentifier, 
	@NameInvalid bit, 
	@NameIllegitimate bit, 
	@NameMisapplied bit, 
	@NameProParte bit, 
	@NameCounter int,
	@user nvarchar(50)
AS

	if (@NameGuid is null) set @nameGuid = newid()
	
	insert tblName
	select @NameGUID, 
		@NameLSID, 
		@NameFull, 
		@NameRank, 
		@NameRankFk, 
		@NameParentFk,
		@NameParentName,
		@NamePreferredFk,
		@NamePreferredName,
		@NameCanonical, 
		@NameAuthors, 
		@NameBasionymAuthors, 
		@NameCombinationAuthors, 
		@NamePublishedIn, 
		@NameReferenceFk, 
		@NameYear, 
		@NameMicroReference, 
		@NameTypeVoucher, 
		@NameTypeName, 
		@NameTypeNameFk, 
		@NameOrthography, 
		@NameBasionym, 
		@NameBasionymFk, 
		@NameBasedOn, 
		@NameBasedOnFk, 
		@NameConservedAgainst, 
		@NameConservedAgainstFk, 
		@NameHomonymOf, 
		@NameHomonymOfFk, 
		@NameReplacementFor, 
		@NameReplacementForFk, 
		@NameInvalid, 
		@NameIllegitimate, 
		@NameMisapplied, 
		@NameProParte, 
		getdate(), 
		@user, 
		null, null,
		@NameCounter 

	

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_Provider
	@providerId int,
	@providerName nvarchar(250),
	@providerHomeUrl nvarchar(250),
	@providerProjectUrl nvarchar(250),
	@providerContactName nvarchar(100),
	@providerContactPhone nvarchar(100),
	@providerContactEmail nvarchar(200),
	@providerContactAddress nvarchar(250),
	@providerNameFull nvarchar(4000),
	@providerStatement ntext,
	@providerIsEditor bit,
	@providerUseForParentage bit,
	@user nvarchar(50)
AS

	if (@providerId = -1)
	begin
		insert tblProvider
		select @providerName,
			@providerHomeUrl,
			@providerProjectUrl,
			@providerContactName,
			@providerContactEmail,
			@providerContactPhone,
			@providerContactAddress,
			@providerNameFull,
			@providerStatement,
			@providerIsEditor,
			@providerUseForParentage,
			getdate(),
			@user,
			null, null
			 
	end
	else
	begin
		update tblProvider
		set ProviderName = @providerName,
			ProviderHomeUrl = @providerHomeUrl,
			ProviderProjectUrl = @providerProjectUrl,
			ProviderContactName = @providerContactName,
			ProviderContactEmail = @providerContactEmail,
			ProviderContactPhone = @providerContactPhone,
			ProviderContactAddress = @providerContactAddress,
			ProviderNameFull = @providerNameFull,
			ProviderStatement = @providerStatement,
			ProviderIsEditor = @providerIsEditor,
			ProviderUseForParentage = @providerUseForParentage,
			ProviderUpdatedDate = getdate(),
			ProviderUpdatedBy = @user
		where ProviderPk = @providerId
	end

	select @@identity


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_ProviderConcept
	@PCPk int,
	@PCProviderImportFk int,
	@PCLinkStatus nvarchar(20),
	@PCMatchScore int,
	@PCConceptFk int,
	@PCIsPreferred bit,
	@PCConceptId nvarchar(300),
	@PCName1 nvarchar(4000),
	@PCName1Id nvarchar(300),
	@PCName2 nvarchar(4000),
	@PCName2Id nvarchar(300),
	@PCRelationship nvarchar(300),
	@PCRelationshipId nvarchar(300),
	@PCRelationshipFk int,
	@PCAccordingTo nvarchar(4000),
	@PCAccordingToId nvarchar(300),
	@PCHybridOrder int,
	@PCConceptVersion nvarchar(200),
	@user nvarchar(50)
AS
	
	if (@PCPk = -1)
	begin
		insert tblProviderConcept
		select @PCProviderImportFk,
			@PCLinkStatus,
			@PCMatchScore,
			@PCConceptFk,
			@PCIsPreferred,
			@PCConceptId,
			@PCName1,
			@PCName1Id,
			@PCName2,
			@PCName2Id,
			@PCRelationship,
			@PCRelationshipId,
			@PCRelationshipFk,
			@PCAccordingTo,
			@PCAccordingToId,
			@PCHybridOrder,
			@PCConceptVersion,
			getdate(),
			@user,
			null, null
			
		select @@identity
	end
	else
	begin
	
		exec sprInsert_ProviderConceptChange @pcpk, @user
	
		update tblProviderConcept
		set  PCProviderImportFk = @PCProviderImportFk,
			PCLinkStatus = @PCLinkStatus,
			PCMatchScore = @PCMatchScore,
			PCConceptFk = @PCConceptFk,
			PCIsPreferredConcept = @PCIsPreferred,
			PCConceptId = @PCConceptId,
			PCName1 = @PCName1,
			PCName1Id = @PCName1Id,
			PCName2 = @PCName2,
			PCName2Id = @PCName2Id,
			PCRelationship = @PCRelationship,
			PCRelationshipId = @PCRelationshipId,
			PCRelationshipFk = @PCRelationshipFk,
			PCAccordingTo = @PCAccordingTo,
			PCAccordingToId = @PCAccordingToId,
			PCHybridOrder = @PCHybridOrder,
			PCConceptVersion = @PCConceptVersion,
			PCUpdatedDate = getdate(),
			PCUpdatedBy = @user
		where PCPk = @PCPk
		
		select @PCPk
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_ProviderImport
	@providerImportPk int,
    @providerFk int,
    @importTypeFk int,
    @fileName nvarchar(200),
    @importStatus nvarchar(20),
    @importDate datetime,
    @notes nvarchar(4000),
    @user nvarchar(50)

AS

	if (@providerImportPk = -1)
	begin
		insert tblProviderImport
		select @providerFk,
			@importTypeFk,
			@fileName,
			@importStatus,
			@importDate,
			@notes,
			getdate(),
			@user,
			null, null
	end
	else
	begin
		update tblProviderImport
		set ProviderImportProviderFk = @providerFk,
			ProviderImportImportTypeFk = @importTypeFk,
			ProviderImportFileName = @fileName,
			ProviderImportStatus = @importStatus,
			ProviderImportDate = @importDate,
			ProviderImportNotes = @notes,
			ProviderImportUpdatedDate = getdate(),
			ProviderImportUpdatedBy = @user
		where ProviderImportPk = @providerImportPk
	end

	select @@identity


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_ProviderName
	@PNPk int, 
	@PNNameFk uniqueidentifier, 
	@PNLinkStatus nvarchar(20), 
	@PNNameMatchScore int,  
	@PNProviderImportFk int, 
	@PNProviderUpdatedDate datetime, 
	@PNProviderAddedDate datetime, 
	@PNNameId nvarchar(4000), 
	@PNNameFull nvarchar(300), 
	@PNNameRank nvarchar(50), 
	@PNNameRankFk int, 
	@PNNameCanonical nvarchar(300), 
	@PNNameAuthors nvarchar(300), 
	@PNBasionymAuthors nvarchar(300), 
	@PNCombinationAuthors nvarchar(300), 
	@PNPublishedIn nvarchar(1000), 
	@PNReferenceId nvarchar(4000), 
	@PNReferenceFk uniqueidentifier, 
	@PNYear nvarchar(20), 
	@PNMicroReference nvarchar(150), 
	@PNTypeVoucher ntext, 
	@PNTypeName nvarchar(300), 
	@PNTypeNameId nvarchar(4000), 
	@PNTypeNameFk uniqueidentifier, 
	@PNOrthography nvarchar(300), 
	@PNBasionym nvarchar(300), 
	@PNBasionymId nvarchar(4000), 
	@PNBasionymFk uniqueidentifier, 
	@PNBasedOn nvarchar(300), 
	@PNBasedOnId nvarchar(4000), 
	@PNBasedOnFk uniqueidentifier, 
	@PNConservedAgainst nvarchar(300), 
	@PNConservedAgainstId nvarchar(4000), 	
	@PNConservedAgainstFk uniqueidentifier, 
	@PNHomonymOf nvarchar(300), 
	@PNHomonymOfId nvarchar(4000), 
	@PNHomonymOfFk uniqueidentifier, 
	@PNReplacementFor nvarchar(300), 
	@PNReplacementForId nvarchar(4000), 
	@PNReplacementForFk uniqueidentifier, 
	@PNInvalid bit, 
	@PNIllegitimate bit, 
	@PNMisapplied bit, 
	@PNProParte bit, 
	@PNGeographyText ntext, 
	@PNGeographyCodes nvarchar(4000), 
	@PNClimate ntext, 
	@PNLifeform ntext, 
	@PNIUCN ntext, 
	@PNNotes ntext, 
	@PNStatusNotes nvarchar(2000), 
	@PNNonNotes ntext,
	@PNQualityStatement nvarchar(4000),
	@PNNameVersion nvarchar(200),
	@user nvarchar(50)
AS
	
	if (@PNPk = -1 )
	begin
		insert into tblProviderName
		select @PNNameFk, 
			@PNLinkStatus, 
			@PNNameMatchScore, 
			@PNProviderImportFk, 
			@PNProviderUpdatedDate, 
			@PNProviderAddedDate, 
			@PNNameId, 
			@PNNameFull, 
			@PNNameRank, 
			@PNNameRankFk, 
			@PNNameCanonical, 
			@PNNameAuthors, 
			@PNBasionymAuthors, 
			@PNCombinationAuthors, 
			@PNPublishedIn, 
			@PNReferenceId, 
			@PNReferenceFk, 
			@PNYear, 
			@PNMicroReference, 
			@PNTypeVoucher, 
			@PNTypeName, 
			@PNTypeNameId, 
			@PNTypeNameFk, 
			@PNOrthography, 
			@PNBasionym, 
			@PNBasionymId, 
			@PNBasionymFk, 
			@PNBasedOn, 
			@PNBasedOnId, 
			@PNBasedOnFk, 
			@PNConservedAgainst, 
			@PNConservedAgainstId, 
			@PNConservedAgainstFk, 
			@PNHomonymOf, 
			@PNHomonymOfId, 
			@PNHomonymOfFk, 
			@PNReplacementFor, 
			@PNReplacementForId, 
			@PNReplacementForFk, 
			@PNInvalid, 
			@PNIllegitimate, 
			@PNMisapplied, 
			@PNProParte, 
			@PNGeographyText, 
			@PNGeographyCodes, 
			@PNClimate, 
			@PNLifeform, 
			@PNIUCN, 
			@PNNotes, 
			@PNStatusNotes, 
			@PNNonNotes,
			@PNQualityStatement,
			@PNNameVersion,
			getdate(),
			@user,
			null, null
		end
		else
		begin
		
			exec sprInsert_ProviderNameChange @pnpk, @user
	
			update tblProviderName
			set PNNameFk = @PNNameFk, 
			PNLinkStatus = @PNLinkStatus, 
			PNNameMatchScore = @PNNameMatchScore, 
			PNProviderImportFk = @PNProviderImportFk, 
			PNProviderUpdatedDate = @PNProviderUpdatedDate, 
			PNProviderAddedDate = @PNProviderAddedDate, 
			PNNameId = @PNNameId, 
			PNNameFull = @PNNameFull, 
			PNNameRank = @PNNameRank, 
			PNNameRankFk = @PNNameRankFk, 
			PNNameCanonical = @PNNameCanonical, 
			PNNameAuthors = @PNNameAuthors, 
			PNBasionymAuthors = @PNBasionymAuthors, 
			PNCombinationAuthors = @PNCombinationAuthors, 
			PNPublishedIn = @PNPublishedIn, 
			PNReferenceId = @PNReferenceId, 
			PNReferenceFk = @PNReferenceFk, 
			PNYear = @PNYear, 
			PNMicroReference = @PNMicroReference, 
			PNTypeVoucher = @PNTypeVoucher, 
			PNTypeName = @PNTypeName, 
			PNTypeNameId = @PNTypeNameId, 
			PNTypeNameFk = @PNTypeNameFk, 
			PNOrthography = @PNOrthography, 
			PNBasionym = @PNBasionym, 
			PNBasionymId = @PNBasionymId, 
			PNBasionymFk = @PNBasionymFk, 
			PNBasedOn = @PNBasedOn, 
			PNBasedOnId = @PNBasedOnId, 
			PNBasedOnFk = @PNBasedOnFk, 
			PNConservedAgainst = @PNConservedAgainst, 
			PNConservedAgainstId = @PNConservedAgainstId, 
			PNConservedAgainstFk = @PNConservedAgainstFk, 
			PNHomonymOf = @PNHomonymOf, 
			PNHomonymOfId = @PNHomonymOfId, 
			PNHomonymOfFk = @PNHomonymOfFk, 
			PNReplacementFor = @PNReplacementFor, 
			PNReplacementForId = @PNReplacementForId, 
			PNReplacementForFk = @PNReplacementForFk, 
			PNInvalid = @PNInvalid, 
			PNIllegitimate = @PNIllegitimate, 
			PNMisapplied = @PNMisapplied, 
			PNProParte = @PNProParte, 
			PNGeographyText = @PNGeographyText, 
			PNGeographyCodes = @PNGeographyCodes, 
			PNClimate = @PNClimate, 
			PNLifeform = @PNLifeform, 
			PNIUCN = @PNIUCN, 
			PNNotes = @PNNotes, 
			PNStatusNotes = @PNStatusNotes, 
			PNNonNotes = @PNNonNotes,
			PNQualityStatement = @PNQualityStatement,
			PNNameVersion = @PNNameVersion,
			PNUpdatedDate = getdate(),
			PNUpdatedBy = @user
		where PNPk = @PNPk
	end
	
	select SCOPE_IDENTITY()
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_ProviderOtherData
	@otherDataPk int,
	@providerImportFk int,
	@dataType nvarchar(100),
	@dataTypeFk int,
	@dataName nvarchar(100),
	@dataRecordId nvarchar(300),
	@dataVersion nvarchar(200),
	@data ntext,
	@xml ntext,
	@user nvarchar(50)
AS

	if (@otherDataPk = -1)
	begin
		insert tblProviderOtherData
		select @providerImportFk,
			@dataType, 
			@dataTypeFk,
			@dataName,
			@dataRecordId,
			@dataVersion,
			@data,
			@xml,
			getdate(),
			@user,
			null, null			
	end
	else
	begin
		update tblProviderOtherData
		set POtherDataProviderImportFk = @providerImportFk,
			POtherDataType = @dataType,
			POtherDataTypeFk = @dataTypeFk,
			POtherDataName = @dataName,
			POtherDataRecordId = @dataRecordId,
			POtherDataVersion = @dataVersion,
			POtherDataData = @data,
			POtherDataXml = @xml,
			POtherDataUpdatedDate = getdate(),
			POtherDataUpdatedBy = @user
		where POtherDataTextPk = @otherDataPk
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_ProviderRIS
	@PRISPk int,
	@PRISProviderReferenceFK int, 
	@PRISRISFk int, 
	@PRISId nvarchar(300),
	@PRISType nvarchar(50), 
	@PRISAuthors nvarchar(4000), 
	@PRISTitle ntext, 
	@PRISDate char(10), 
	@PRISNotes ntext, 
	@PRISKeywords nvarchar(1000), 
	@PRISStartPage nvarchar(100), 
	@PRISEndPage nvarchar(100), 
	@PRISJournalName nvarchar(500), 
	@PRISStandardAbbreviation nvarchar(150), 
	@PRISVolume nvarchar(50), 
	@PRISIssue nvarchar(50), 
	@PRISCityOfPublication nvarchar(200), 
	@PRISPublisher nvarchar(500), 
	@PRISISSNNumber nvarchar(500), 
	@PRISWebUrl nvarchar(1000), 
	@PRISTitle2 ntext, 
	@PRISTitle3 ntext, 
	@PRISAuthors2 nvarchar(4000), 
	@PRISAuthors3 nvarchar(4000), 
	@RefId nvarchar(300), --PRReferenceId for the assoc ref
	@user nvarchar(50)

AS
	
	if (@PRISPk = -1)
	begin
		--link to provider reference
		if (@PRISProviderReferenceFk is null)
		begin
			select @PRISProviderReferenceFk = PRPk
			from tblProviderReference
			where PRReferenceId = @RefId
		end
		
		insert tblProviderRIS
		select @PRISProviderReferenceFK, 		
			@PRISRISFk, 
			@PRISId,
			@PRISType, 
			@PRISAuthors,
			@PRISTitle, 
			@PRISDate, 
			@PRISNotes,
			@PRISKeywords,
			@PRISStartPage,
			@PRISEndPage,
			@PRISJournalName,
			@PRISStandardAbbreviation,
			@PRISVolume,
			@PRISIssue,
			@PRISCityOfPublication,
			@PRISPublisher,
			@PRISISSNNumber,
			@PRISWebUrl,
			@PRISTitle2,
			@PRISTitle3,
			@PRISAuthors2,
			@PRISAuthors3,
			getdate(),
			@user,
			null, null
			
			select @@identity
	end
	else
	begin
	
		exec sprInsert_ProviderRISChange @prispk, @user
	
		update tblProviderRIS
		set PRISProviderReferenceFK = @PRISProviderReferenceFK, 
			PRISRISFk = @PRISRISFk, 
			PRISId = @PRISId,
			PRISType = @PRISType, 
			PRISAuthors = @PRISAuthors,
			PRISTitle = @PRISTitle, 
			PRISDate = @PRISDate, 
			PRISNotes = @PRISNotes,
			PRISKeywords = @PRISKeywords,
			PRISStartPage = @PRISStartPage,
			PRISEndPage = @PRISEndPage,
			PRISJournalName = @PRISJournalName,
			PRISStandardAbbreviation = @PRISStandardAbbreviation,
			PRISVolume = @PRISVolume,
			PRISIssue = @PRISIssue,
			PRISCityOfPublication = @PRISCityOfPublication,
			PRISPublisher = @PRISPublisher,
			PRISISSNNumber = @PRISISSNNumber,
			PRISWebUrl = @PRISWebUrl,
			PRISTitle2 = @PRISTitle2,
			PRISTitle3 = @PRISTitle3,
			PRISAuthors2 = @PRISAuthors2,
			PRISAuthors3 = @PRISAuthors3,
			PRISUpdatedDate = getdate(),
			PRISUpdatedBy = @user
		where PRISPk = @PRISPk
		
		select @PRISPk
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_ProviderReference
	@PRPk int,
	@PRProviderImportFk int,
	@PRReferenceId nvarchar(4000),
	@PRReferenceFk uniqueidentifier,
	@PRLinkStatus nvarchar(20),
	@PRCitation nvarchar(4000),
	@PRFullCitation ntext,
	@PRXML ntext,
	@PRReferenceVersion nvarchar(200),
	@user nvarchar(50)	
	
AS
	
	if (@PRPk is null or @PRPk = -1)
	begin
		insert tblProviderReference
		select @PRProviderImportFk,
			@PRReferenceId,
			@PRReferenceFk,
			@PRLinkStatus,
			@PRCitation,
			@PRFullCitation,
			@PRXML,
			@PRReferenceVersion,
			getdate(),
			@user,
			null, null
	end
	else
	begin
	
		exec sprInsert_ProviderReferenceChange @prpk, @user
	
		update tblProviderReference
		set PRProviderImportFk = @PRProviderImportFk,
			PRReferenceId = @PRReferenceId,
			PRReferenceFk = @PRReferenceFk,
			PRLinkStatus = @PRLinkStatus,
			PRCitation = @PRCitation,
			PRFullCitation = @PRFullCitation,
			PRXML = @PRXML,
			PRReferenceVersion = @PRReferenceVersion,
			PRUpdatedDate = getdate(),
			PRUpdatedBy = @user
		where PRPk = @PRPk
	end

	select SCOPE_IDENTITY()
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsertUpdate_SystemProviderConcept
	@PCPk int,
	@PCProviderImportFk int,
	@PCLinkStatus nvarchar(20),
	@PCMatchScore int,
	@PCConceptFk int,
	@PCIsPreferred bit,
	@PCConceptId nvarchar(300),
	@PCName1 nvarchar(4000),
	@PCName1Id nvarchar(300),
	@PCName2 nvarchar(4000),
	@PCName2Id nvarchar(300),
	@PCRelationship nvarchar(300),
	@PCRelationshipId nvarchar(300),
	@PCRelationshipFk int,
	@PCAccordingTo nvarchar(4000),
	@PCAccordingToId nvarchar(300),
	@PCHybridOrder int,
	@PCConceptVersion nvarchar(200),
	@user nvarchar(50)
AS

	--for system record we need to set all text fields to the source provider name/ref details
	declare @provFk int
	select @provFk = ProviderImportProviderFk 
	from tblProviderImport
	where ProviderImportPk = @pcproviderImportfk
	
	select @PCName1 = NameFull 
	from vwProviderName pn
	inner join tblName n on n.NameGuid = pn.PNNameFk
	where PNNameId = @PCName1Id and ProviderPk = @provFk
	
	select @PCName2 = NameFull 
	from vwProviderName pn
	inner join tblName n on n.NameGuid = pn.PNNameFk 
	where PNNameId = @PCName2Id and ProviderPk = @provFk
	
	select @PCAccordingTo = ReferenceCitation 
	from vwProviderReference pr
	inner join tblReference r on r.ReferenceGuid = pr.PRReferenceFk
	where PRReferenceId = @PCAccordingToId and ProviderPk = @provFk
	
	select @PCrelationship = RelationshipTypeName
	from tblRelationshipType
	where RelationshipTypePk = @PCRelationshipFk
	
		
	if (@PCPk = -1)
	begin
			
		insert tblProviderConcept
		select @PCProviderImportFk,
			@PCLinkStatus,
			@PCMatchScore,
			@PCConceptFk,
			@PCIsPreferred,
			@PCConceptId,
			@PCName1,
			@PCName1Id,
			@PCName2,
			@PCName2Id,
			@PCRelationship,
			@PCRelationshipId,
			@PCRelationshipFk,
			@PCAccordingTo,
			@PCAccordingToId,
			@PCHybridOrder,
			@PCConceptVersion,
			getdate(),
			@user,
			null, null
					
		
		select @@identity
	end
	else
	begin
		update tblProviderConcept
		set  PCProviderImportFk = @PCProviderImportFk,
			PCLinkStatus = @PCLinkStatus,
			PCMatchScore = @PCMatchScore,
			PCConceptFk = @PCConceptFk,
			PCIsPreferredConcept = @PCIsPreferred,
			PCConceptId = @PCConceptId,
			PCName1 = @PCName1,
			PCName1Id = @PCName1Id,
			PCName2 = @PCName2,
			PCName2Id = @PCName2Id,
			PCRelationship = @PCRelationship,
			PCRelationshipId = @PCRelationshipId,
			PCRelationshipFk = @PCRelationshipFk,
			PCAccordingTo = @PCAccordingTo,
			PCAccordingToId = @PCAccordingToId,
			PCHybridOrder = @PCHybridOrder,
			PCConceptVersion = @PCConceptVersion,
			PCUpdatedDate = getdate(),
			PCUpdatedBy = @user
		where PCPk = @PCPk
		
		select @PCPk
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_Concept
	@providerPk int,
	@name1 nvarchar(4000),
	@name1Id nvarchar(300),
	@name2 nvarchar(4000),
	@name2Id nvarchar(300),
	@accordingTo nvarchar(4000),
	@accordingToId nvarchar(300),
	@relationship nvarchar(300),
	@relationshipFk int,
	@hybridOrder int,
	@user nvarchar(50)
AS

	declare @name1Fk uniqueidentifier, @name2Fk uniqueidentifier, @accToFk uniqueidentifier, @newId int
	
	--get fks for names and ref (the ProviderName and Providerref Ids are passed into this SP)
	select @name1Fk = PNNameFk from vwProviderName where PNNameId = @name1Id and ProviderPk = @providerPk
	select @name2Fk = PNNameFk from vwProviderName where PNNameId = @name2Id and ProviderPk = @providerPk
	select @accToFk = PRReferenceFk from tblProviderReference where PRReferenceId = @accordingToId
	
	insert tblConcept
	select @name1,
		@name1Fk,
		@name2,
		@name2Fk, 
		@relationship,
		@relationshipFk,
		@accordingTo,
		@accToFk,
		@hybridOrder,
		getdate(),
		@user, 
		null, null
	
	
	select @newId = @@identity
	
	--if this is a parent or preferred concept then update the assoc. tblname fields
	if (@relationshipFk = 6)
	begin
		update tblName 
		set NameParentFk = @name2Fk, NameParent = @name2
		where NameGuid = @name1Fk
	end
	if (@relationshipFk = 15)
	begin
		update tblName set NamePreferredFk = @name2Fk, NamePreferred = @name2
		where NameGuid= @name1Fk
	end
	
	select * from tblConcept where ConceptPk = @newId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_ProviderConceptChange
	@pcpk int,
	@user nvarchar(50)
AS

	insert tblProviderConcept_Change
	select *, getdate(), @user
	from tblProviderConcept
	where PCPk = @pcpk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_ProviderNameChange
	@pnpk int,
	@user nvarchar(50)
AS

	insert tblProviderName_Change
	select *, getdate(), @user
	from tblProviderName
	where PNPk = @pnpk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_ProviderRISChange
	@prispk int,
	@user nvarchar(50)
AS

	insert tblProviderRIS_Change
	select *, getdate(), @user
	from tblProviderRIS
	where PRISPk = @prispk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_ProviderReferenceChange
	@prpk int,
	@user nvarchar(50)
AS

	insert tblProviderReference_Change
	select *, getdate(), @user
	from tblProviderReference
	where PRPk = @prpk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_Reference
	@referenceGuid uniqueidentifier,
	@referenceLSID nvarchar(300),
	@referenceCitation nvarchar(4000),
	@referenceFullCitation ntext,
	@user nvarchar(50)
AS

	insert tblReference
	select @referenceGuid,
		@referenceLSID,
		@referenceCitation,
		@referenceFullCitation,
		getdate(),
		@user,
		null, null


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_ReferenceRIS
	@referenceGuid uniqueidentifier,	
	@PRPk int,
	@PRISPk int,
	@user nvarchar(50)
AS

	--delete any existing RIS record, so we dont end up with multiple
	delete tblReferenceRIS
	where RISReferenceFk = @referenceGuid


	--insert RIS values
	insert tblReferenceRIS
	select @referenceGuid, 
		PRISId,
		PRISType, 
		PRISTitle, 
		PRISAuthors, 
		PRISDate, 
		PRISNotes, 
		PRISKeywords, 
		PRISStartPage, 
		PRISEndPage, 
		PRISJournalName, 
		PRISStandardAbbreviation, 
		PRISVolume, 
		PRISIssue, 
		PRISCityOfPublication, 
		PRISPublisher, 
		PRISISSNNumber, 
		PRISWebUrl, 
		PRISTitle2, 
		PRISTitle3, 
		PRISAuthors2, 
		PRISAuthors3, 
		getdate(),
		@user,
		null, null 
	from tblProviderRIS
	where PRISProviderReferenceFk = @PRPk
	
	declare @newId int
	select @newId = @@identity	
	
	--update Provider RIS record
	update tblProviderRIS 
	set PRISRISFk = @newId
	where PRISPk = @PRISPk
	
	select @newId
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprInsert_ReportError
	@reportFk int,
	@tableName nvarchar(100),
	@recordId nvarchar(300),
	@statusFk int,
	@comment nvarchar(4000)
AS

	insert tblReportError
	select @tableName, 
		@recordId,
		@reportFk,
		@statusFk,
		@comment


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprLogin
	@userPk int,
	@pwd nvarchar(200)
AS

	declare @realPwd varchar(200)
	select @realPwd = cast(UserPassword as varchar(200))
	from tblUser 
	where UserPk = @userPk
	
	select case when @pwd is null and @realPwd is null then cast(1 as bit)
		when @realPwd = @pwd then cast(1 as bit) else cast(0 as bit) end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSearch_References
	@searchTxt nvarchar(1000)
AS

	select *
	from tblReference
	where ReferenceCitation like '%' + @searchTxt + '%' 
		or ReferenceLSID like '%' + @searchTxt + '%'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ConceptMatches
	@PCPk int
AS

	declare @name1Id nvarchar(300), @name2Id nvarchar(300), @relFk int, @accToId nvarchar(4000), @accToFk uniqueidentifier
	declare @name2Fk uniqueidentifier, @name1Fk uniqueidentifier, @provPk int
		
	select @name1Id = PCName1Id, @name2Id = PCName2Id, @relFk = PCRelationshipFk, @accToId = PCAccordingToId,
		@provPk = ProviderPk
	from vwProviderConcept
	where PCPk = @PCPk
	
	select @name1Fk = PNNameFk from vwProviderName where PNNameId = @name1Id and ProviderPk = @provPk
	select @name2Fk = PNNameFk from vwProviderName where PNNameId = @name2Id and ProviderPk = @provPk
	select @accToFk = PRReferenceFk from tblProviderReference where PRReferenceId = @accToId
		
	if (@name2Fk is not null and @relFk is not null)
	begin
		select * 
		from tblConcept
		where ConceptName1Fk = @name1Fk and ConceptName2Fk = @name2Fk 
				and ConceptRelationshipFk = @relFk and 
				(@accToFk is null or ConceptAccordingToFk = @accToFk)
	end
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.sprSelect_FieldStatus

@FieldStatusCounterPK int

AS
	/* SET NOCOUNT ON */
	
	SELECT fs.*, fsi.*, fsl.*,
		fs.FieldStatusCreatedBy as AddedBy,
		fs.FieldStatusCreatedDate as FieldStatusAddedDate,
		fs.FieldStatusUpdatedBy as UpdatedBy
	FROM tblFieldStatus fs
	LEFT JOIN tblFieldStatusIdentifier fsi ON fs.FieldStatusIdentifierFK = fsi.FieldStatusIdentifierCounterPK
	LEFT JOIN tblFieldStatusLevel fsl ON fs.FieldStatusLevelFK = fsl.FieldStatusLevelCounterPK
	WHERE FieldStatusCounterPK = @FieldStatusCounterPK
	
	RETURN @@ERROR



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.sprSelect_FieldStatusIdentifiers

AS
	/* SET NOCOUNT ON */
	
	SELECT * 
	FROM tblFieldStatusIdentifier
	ORDER BY FieldStatusIdentifierGroup, FieldStatusIdentifierFieldName
	
	RETURN @@ERROR



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.sprSelect_FieldStatusLevels

AS
	/* SET NOCOUNT ON */
	
	SELECT *
	FROM tblFieldStatusLevel
	
	RETURN @@ERROR



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.sprSelect_FieldStatus_RecordByKeyAndTableName

@FieldStatusRecordFK				varchar(50),
@FieldStatusIdentifierTableName		varchar(50)

AS
	/* SET NOCOUNT ON */
	
	SELECT fs.FieldStatusCreatedBy as AddedBy,
        fs.FieldStatusCreatedDate as FieldStatusAddedDate,
        fs.FieldStatusComment,
        fs.FieldStatusCounterPK,
        fs.FieldStatusIdentifierFK,
        fs.FieldStatusLevelFK,
        FieldStatusRecordFk,
        fs.FieldStatusUpdatedDate,
        fs.FieldStatusUpdatedBy as UpdatedBy,
        fsi.*, 
        fsl.*        
	FROM tblFieldStatus fs
	LEFT JOIN tblFieldStatusIdentifier fsi ON fs.FieldStatusIdentifierFK = fsi.FieldStatusIdentifierCounterPK
	LEFT JOIN tblFieldStatusLevel fsl ON fs.FieldStatusLevelFK = fsl.FieldStatusLevelCounterPK
	WHERE FieldStatusRecordFK = @FieldStatusRecordFK
	AND fsi.FieldStatusIdentifierTableName = @FieldStatusIdentifierTableName
	
	RETURN @@ERROR



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ImportTypes
	
AS

	select * from tblImportType


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.sprSelect_Literature_Search3

@Where				varchar(2000) = '',
@OrderBy			varchar(2000) = '',
--@TotalCount			int = NULL OUTPUT
@DoCount			bit = 0

AS
	/* SET NOCOUNT ON */
	
	--Testing
	--DECLARE @Where2 nvarchar(1000)
	--SET @Where2 = N' WHERE  (EXISTS (SELECT TOP 1 ReferenceFieldPk FROM tblReferenceField WHERE ReferenceFieldReferenceFk = ReferenceGuid AND ReferenceFieldText LIKE ''%WE%'' ))  OR  (EXISTS (SELECT TOP 1 ReferenceFieldPk FROM tblReferenceField WHERE ReferenceFieldReferenceFk = ReferenceGuid AND ReferenceFieldText LIKE ''%TREE%'' )) ' 
	--SET @Where = @Where2
	
	IF @OrderBy IS NULL OR Rtrim(Ltrim(@OrderBy)) = ''
		SET @OrderBy = ' ORDER BY AuthorPrimary, DatePrimary '
	
		
	DECLARE @nvcSelect		varchar(2000)
	DECLARE @nvcCount		varchar(8000)
	DECLARE @nvcFrom		varchar(2000)

	
	
	DECLARE @nvcQuery 			varchar(8000)
	
	SET @nvcSelect = ' SELECT DISTINCT null as ReferenceAddedByFk,
		r.ReferenceAddedDate,
		null as ReferenceCounter,
		r.ReferenceCitation as ReferenceGenCitation,
		null as ReferenceGenParentCitation,
		cast(r.ReferenceGuid as varchar(38)) as ReferenceGuid,
		null as ReferenceIsDeleted,
		null as ReferenceLoadId,
		null as ReferenceLoadOwnerFk,
		null as ReferenceNeedsProcessing,
		null as ReferenceParentGuid,
		null as ReferenceReferenceTypeFk,
		null as ReferenceTempOldKey,
		null as ReferenceUpdatedByFk,
		r.ReferenceUpdatedDate, 
		r.ReferenceCitation as Citation,
		null as ReferenceTypePk,
		null as ReferenceTypeText,
		r.ReferenceAddedBy AS AddedBy,
		r.ReferenceUpdatedBy AS UpdatedBy, 
		null as ParentCitation,
		null AS DatePrimary,
		null AS AuthorPrimary'				
					
	SET @nvcFrom = ' FROM tblReference r '
		
	
	IF @DoCount = 1
		BEGIN
		PRINT 'Count'
		--CREATE TABLE #tmpCount (CountCount	int)
		SET @nvcCount = ' SELECT COUNT(*) ' + @nvcFrom + @Where
		EXEC(@nvcCount)		
		--SELECT TOP 1 @TotalCount = CountCount FROM #tmpCount
		END
	ELSE
		BEGIN
		PRINT 'No Count'
		SET @nvcQuery = @nvcSelect + @nvcFrom + @Where + @OrderBy
		PRINT @nvcQuery
		EXEC(@nvcQuery)
		END
	
	RETURN @@ERROR



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Name
	@nameGuid uniqueidentifier
AS

	select cast(NameGUID as varchar(38)) as NameGuid, 
		NameLSID, 
		NameFull, 
		NameRank, 
		NameRankFk, 
		cast(NameParentFk as varchar(38)) as NameParentFk,
		NameParent,
		cast(NamePreferredFk as varchar(38)) as NamePreferredFk,
		NamePreferred,
		NameCanonical, 
		NameAuthors, 
		NameBasionymAuthors, 
		NameCombinationAuthors, 
		NamePublishedIn, 
		cast(NameReferenceFk as varchar(38)) as NameReferenceFk, 
		NameYear, 
		NameMicroReference, 
		NameTypeVoucher, 
		NameTypeName, 
		cast(NameTypeNameFk as varchar(38)) as NameTypeNameFk, 
		NameOrthography, 
		NameBasionym, 
		cast(NameBasionymFk as varchar(38)) as NameBasionymFk, 
		NameBasedOn, 
		cast(NameBasedOnFk as varchar(38)) as NameBasedOnFk, 
		NameConservedAgainst, 
		cast(NameConservedAgainstFk as varchar(38)) as NameConservedAgainstFk, 
		NameHomonymOf, 
		cast(NameHomonymOfFk as varchar(38)) as NameHomonymOfFk, 
		NameReplacementFor, 
		cast(NameReplacementForFk as varchar(38)) as NameReplacementForFk, 
		NameInvalid, 
		NameIllegitimate, 
		NameMisapplied, 
		NameProParte, 
		NameCreatedDate, 
		NameCreatedBy, 
		NameUpdatedDate, 
		NameUpdatedBy
	from tblName 
	where NameGuid = @nameGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NameConcepts
	@nameGuid uniqueidentifier
AS

	select *
	from tblConcept
	where ConceptName1Fk = @nameGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NameMappings

AS

	select * from tblNameMapping


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NameMatches
	@providerNamePk int
AS

	delete tmpMatchResults
	
	--get match rule set for rank of this name
	declare @matchId nvarchar(50)
	select @matchId = RankMatchRuleSetFk
	from tblProviderName pn 
	inner join tblRank r on r.RankPk = pn.PNNameRankFk
	where pn.PNPk = @providerNamePk 
		
	--execute each match function until either no records in tmpMatchResults or end of match set
		
	declare @matchFuncs table(MatchPk int, MatchFunction nvarchar(1000), Threshold int, PassFk int, FailFk int)
	
	insert into @matchFuncs
	select MatchPk, MatchFunction, MatchThreshold, MatchPassFk, MatchFailFk
	from tblMatch
	where MatchRuleSet = @matchId
	order by MatchFirst desc
	
	declare @finished bit, @func nvarchar(1000), @pk int, @passFk int, @failFk int, @thHold int, @count int
	set @finished = 0
	select top 1 @pk = MatchPk from @matchFuncs
	
	create table #tmpRes(recId nvarchar(300), matchScore int)
	
	while (@finished = 0)
	begin
		select @pk = MatchPk, @func = MatchFunction, @thHold = Threshold, @passFk = PassFk, @failFk = FailFk
		from @matchFuncs 
		where MatchPk = @pk
	
		--tmp store current results
		delete #tmpRes
		insert #tmpRes select * from tmpMatchResults
			
		--build up stored proc call
		declare @spCall nvarchar(4000)
		set @spCall = 'exec ' + @func + ' ' + cast(@providerNamePk as nvarchar(50)) + ', ' + cast(@thHold as varchar(10)) 
		--todo other params, are they required?
		print(@spCall)
		exec(@spCall)						
				
		select @count = count(*) from tmpMatchResults
		
		if (@count = 0) 
		begin
			-- if failed then restore previous results, then continue to the failFk match row
			set @pk = @failFk
			if (@pk is not null)
			begin
				insert tmpMatchResults
				select * from #tmpRes
			end
		end
		else set @pk = @passFk
		
		if (@pk is null) set @finished = 1
	end
	
	--return remaining matching names
	select cast(n.NameGUID as varchar(38)) as NameGuid, 
		n.NameLSID, 
		n.NameFull, 
		n.NameRank, 
		n.NameRankFk, 
		cast(n.NameParentFk as varchar(38)) as NameParentFk,
		n.NameParent,
		cast(n.NamePreferredFk as varchar(38)) as NamePreferredFk,
		n.NamePreferred,
		n.NameCanonical, 
		n.NameAuthors, 
		n.NameBasionymAuthors, 
		n.NameCombinationAuthors, 
		n.NamePublishedIn, 
		cast(n.NameReferenceFk as varchar(38)) as NameReferenceFk, 
		n.NameYear, 
		n.NameMicroReference, 
		n.NameTypeVoucher, 
		n.NameTypeName, 
		cast(n.NameTypeNameFk as varchar(38)) as NameTypeNameFk, 
		n.NameOrthography, 
		n.NameBasionym, 
		cast(n.NameBasionymFk as varchar(38)) as NameBasionymFk, 
		n.NameBasedOn, 
		cast(n.NameBasedOnFk as varchar(38)) as NameBasedOnFk, 
		n.NameConservedAgainst, 
		cast(n.NameConservedAgainstFk as varchar(38)) as NameConservedAgainstFk, 
		n.NameHomonymOf, 
		cast(n.NameHomonymOfFk as varchar(38)) as NameHomonymOfFk, 
		n.NameReplacementFor, 
		cast(n.NameReplacementForFk as varchar(38)) as NameReplacementForFk, 
		n.NameInvalid, 
		n.NameIllegitimate, 
		n.NameMisapplied, 
		n.NameProParte, 
		n.NameCreatedDate, 
		n.NameCreatedBy, 
		n.NameUpdatedDate, 
		n.NameUpdatedBy
	from tblName n
	inner join tmpMatchResults r on r.MatchResultRecordId = N.NameGuid

	select avg(MatchResultScore) as MatchScore from tmpMatchResults


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NameOtherData
	@nameGuid uniqueidentifier
AS

	select od.* 
	from vwProviderOtherData od
	inner join vwProviderName pn on pn.PNNameId = od.POtherDataRecordId and pn.ProviderPk = od.ProviderPk
	where pn.PNNameFk = @nameGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NamesWithCanonical
	@providerNamePk int,
	@threshold int
AS

	declare @nameCanonical nvarchar(300)
	select @nameCanonical = lower(PNNameCanonical) from tblProviderName where PNPk = @providerNamePk
	
	if (@namecanonical is null)
	begin
		--fail
		delete tmpMatchResults
		return
	end

	if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
	begin
		insert tmpMatchResults
		select NameGuid, dbo.fnLevenshteinPercentage(lower(NameCanonical), @nameCanonical)
		from tblName 
		where dbo.fnLevenshteinPercentage(lower(NameCanonical), @nameCanonical) >= @threshold
	end
	else
	begin
		delete mr
		from tmpMatchResults mr
		inner join tblName n on n.NameGuid = mr.MatchResultRecordId
		where dbo.fnLevenshteinPercentage(lower(NameCanonical), @nameCanonical) < @threshold
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NamesWithParent
	@providerNamePk int,
	@threshold int
AS

	declare @name1Id nvarchar(300), @parentFk uniqueidentifier, @provPk int
	
	select @name1Id = PNNameId, @provPk = ProviderPk from vwProviderName where PNPk = @providerNamePk
	
	--assumes parent names in the taxon hierarchy have been added
	-- joins to the provider concept records to obtain parent concept, then parent name guid/fk
	select @parentFk = PNNameFk
	from vwProviderConcept pc
	inner join vwProviderName pn on pn.PNNameId = pc.PCName2Id and pn.ProviderPk = pc.ProviderPk
	where PCName1Id = @name1Id and PCRelationshipFk = 6 and pc.ProviderPk = @provPk --parent type
	
	if (@parentFk is not null)
	begin
		if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
		begin
			insert tmpMatchResults
			select n.NameGuid, 100
			from tblName n
			where n.NameParentFk = @parentFk
		end
		else
		begin
			delete mr
			from tmpMatchResults mr
			inner join tblName n on n.NameGuid = mr.MatchResultRecordId
			where n.NameParentFk is null or n.NameParentFk <> @parentFk
		end
	end
	else
	begin
		delete tmpMatchResults
	end
		
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NamesWithPartialAuthors
	@providerNamePk int,
	@threshold int
AS

	declare @nameAuthors nvarchar(1000)
	select @nameAuthors = lower(PNNameAuthors) from tblProviderName where PNPk = @providerNamePk
	
	if (@nameAuthors is null)
	begin
		--fail
		delete tmpMatchResults
		return
	end

	if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
	begin
		insert tmpMatchResults
		select NameGuid, dbo.fnPartialAuthorMatch(lower(NameAuthors), @nameAuthors)
		from tblName 
		where dbo.fnPartialAuthorMatch(lower(NameAuthors), @nameAuthors) >= @threshold
	end
	else
	begin
		delete mr
		from tmpMatchResults mr
		inner join tblName n on n.NameGuid = mr.MatchResultRecordId
		where dbo.fnPartialAuthorMatch(lower(NameAuthors), @nameAuthors) < @threshold
	end

	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NamesWithRank
	@providerNamePk int,
	@threshold int --ignored - must match rank fk exaclty
AS

	declare @nameRankFk int
	select @nameRankFk = PNNameRankFk from tblProviderName
	where PNPk = @providerNamePk
	
	if (@nameRankFk is null)
	begin
		--fail
		delete tmpMatchResults
		return
	end
	

	if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
	begin
		insert tmpMatchResults
		select NameGuid, 100
		from tblName 
		where NameRankFk = @nameRankFk
	end
	else
	begin
		delete mr
		from tmpMatchResults mr
		inner join tblName n on n.NameGuid = mr.MatchResultRecordId
		where n.NameRankFk <> @nameRankFk
	end
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NamesWithRankAndYear
	@providerNamePk int,
	@threshold int 
AS

	declare @nameRankFk int, @nameYear nvarchar(20)
	select @nameRankFk = PNNameRankFk, @nameYear = PNYear from tblProviderName
	where PNPk = @providerNamePk
	
	if (@nameRankFk is null or @nameYear is null)
	begin
		--fail
		delete tmpMatchResults
		return
	end
	

	if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
	begin
		insert tmpMatchResults
		select NameGuid, 100
		from tblName 
		where NameRankFk = @nameRankFk and NameYear = @nameYear
	end
	else
	begin
		delete mr
		from tmpMatchResults mr
		inner join tblName n on n.NameGuid = mr.MatchResultRecordId
		where n.NameRankFk <> @nameRankFk or n.NameYear <> @nameYear
	end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NamesWithYear
	@providerNamePk int,
	@threshold int
AS

	declare @nameYear nvarchar(20)
	select @nameYear = lower(PNYear) from tblProviderName where PNPk = @providerNamePk
	
	
	if (@nameYear is null)
	begin
		--fail
		delete tmpMatchResults
		return
	end
	

	if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
	begin
		insert tmpMatchResults
		select NameGuid, dbo.fnLevenshteinPercentage(lower(NameYear), @nameYear)
		from tblName 
		where dbo.fnLevenshteinPercentage(lower(NameYear), @nameYear) >= @threshold
	end
	else
	begin
		delete mr
		from tmpMatchResults mr
		inner join tblName n on n.NameGuid = mr.MatchResultRecordId
		where dbo.fnLevenshteinPercentage(lower(NameYear), @nameYear) < @threshold
	end

	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_NoNames
	@providerNamePk int,
	@threshold int
AS

	--test sp to fake a mismatch
	delete tmpMatchResults


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


-- returns a selection of nodes from the start node up through its ancestors to the root
-- uses a cursor
-- restricted by permissions of role accessing

CREATE PROCEDURE dbo.sprSelect_NodeToRoot

@StartNodeKey			uniqueidentifier, 
@intClassificationKey	int = 0,
@intRoleKey				int
	
AS

/* SET NOCOUNT ON */

DECLARE @tableResults	TABLE(NodeKey uniqueidentifier, NodeLevel int, NodeRolePk int )
DECLARE @ParentKey		uniqueidentifier
DECLARE @NodeKey		uniqueidentifier
DECLARE @intCount		int
DECLARE @GrandParent	uniqueidentifier
DECLARE @intLevel		int
declare @nodeRolePk     int
	
--IF @intClassificationKey IS NULL OR @intClassificationKey = 0 
--	BEGIN
	
	
	SELECT @ParentKey = NameParentFk, @NodeKey = NameGuid
	FROM         tblName 
	WHERE NameGuid = @StartNodeKey
	
	SET @intLevel = 0
	
 	SELECT @intCount = COUNT(*) FROM @tableResults WHERE NodeKey = @ParentKey
	
	WHILE @intCount < 1 
	AND (NOT @NodeKey IS NULL)
	BEGIN
		set @nodeRolePk = 1
	    --select top 1 @nodeRolePk = NodeRolePk 
	    --from tblNodeRole
	    --where NodeRoleNodeFk = @NodeKey and NodeRoleRoleFk = @intRoleKey
	    
		SET @intLevel = @intLevel + 1
		INSERT INTO @tableResults VALUES(@NodeKey, @intLevel, @nodeRolePk)
		
		SELECT @GrandParent = NameParentFk
		FROM         tblName 
		WHERE NameGuid = @ParentKey
		
		SET @NodeKey = @ParentKey
		SET @ParentKey = @GrandParent
		
		SELECT @intCount = COUNT(*) FROM @tableResults WHERE NodeKey = @NodeKey
	END
	
	
	SELECT n1.NameCreatedBy as NameAddedBy,
		n1.NameCreatedDate as NameAddedDate,
		null as NameAggregate,
		null as NameAnamorphGenusFK,
		n1.NameAuthors, 
		0 as NameAutonym,
		cast(n1.NameBasedOnFK as varchar(38)) as NameBasedOnFk,
		cast(n1.NameBasionymFK as varchar(38)) as NameBasionymFk,
		null as NameBlockingFK,
		isnull(n1.NameCanonical, '[unnamed]') as NameCanonical,
		null as NameCheckStatus,
		null as NameClassificationFK,
		cast(n1.NamePreferredFk as varchar(38)) as NameCurrentFK,
		0 as NameDubium,
		isnull(n1.NameFull, '') as NameFull,
		null as NameHybridLink,
		isnull(n1.NameIllegitimate, 0) as NameIllegitimate,
		0 as NameInCitation,
		isnull(n1.NameInvalid, 0) as NameInvalid,
		0 as NameIsAnamorph,
		isnull(n1.NameMisapplied, 0) as NameMisapplied,
		'ICBN' as NameNomCode,
		0 as NameNovum,
		n1.NameOrthography as NameOrthographyVariant,
		null as NameOwner,
		null as NamePage,
		cast(n1.NameParentFk as varchar(38)) as NameParentFK,
		null as NamePrimaryOwnerFk,
		isnull(n1.NameProParte, 0) as NameProParte,
		cast(n1.NameReferenceFK as varchar(38)) as NameReferenceFk,
		null as NameSanctioningAuthor,
		null as NameSanctioningPage,
		null as NameSanctioningReferenceFK,
		0 as NameSuppress,
		null as NameTaxonomyReferenceFK,
		isnull(n1.NameRankFk,1) as NameTaxonRankFk,
		null as NameTempRepKey,
		null as NameTypeLocality,
		cast(n1.NameTypeNameFk  as varchar(38))as NameTypeTaxonFK,
		n1.NameUpdatedBy,
		null as NameUpdatedByFK,
		n1.NameUpdatedDate,
		cast(n1.NameGuid as varchar(38)) as NameGuid,
		n1.NameYear as NameYearOfPublication, 
		null as NameYearOnPublication,  
		n1.NameParent as ParentName, 
		n1.NameBasionym as BasionymName, 
		null as BasionymDate,
		isnull(n1.NameLSID,'') as NameLSID,
		tr.RankName as TaxonRankName, tr.RankAbbreviation as TaxonRankAbbreviation,
		
    	b.NodeLevel,  
    	@StartNodeKey as SeedNode,
    	
		--rn.NodeRolePk, 		
		--p1.*, 
		
		1 as PermissionRead,
		1 as PermissionWrite,
		1 as PermissionsAddChildren,
		1 as PermissionsChangeLinks,
		1 as PermissionsModifyPermission
		
		--p2.PermissionsPk as InheritedPermissionsPk,
		--p2.PermissionsRead as InheritedPermissionsRead,
		--p2.PermissionsWrite as InheritedPermissionsWrite,
		--p2.PermissionsAddChildren as InheritedPermissionsAddChildren,
		--p2.PermissionsChangeLinks as InheritedPermissionsChangeLinks,
		--p2.PermissionsModifyPermission as InheritedPermissionsModifyPermission,
		
	FROM @tableResults b 
	    LEFT JOIN tblName n1 ON n1.NameGuid = b.NodeKey
	    left join tblName pn on pn.NameGuid = n1.NameParentFk
		--LEFT JOIN tblNodeRole rn on rn.NodeRolePk = b.NodeRolePk
		--LEFT JOIN tblPermissions p1 ON p1.PermissionsPk = rn.NodeRolePermissionsFk
		--LEFT JOIN tblPermissions p2 ON p2.PermissionsPk = rn.NodeRoleInheritedPermissionsFk 
		LEFT JOIN tblRank tr ON n1.NameRankFk = tr.RankPk
	ORDER BY b.NodeLevel DESC
	
	RETURN @@error
	--END
--ELSE --Alternate Hierarchy
	--todo??



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ParentProviderName
	@PNPk int
AS
	--select parent concept of provider name, using the concepts
	
	select p.* 
	from vwProviderName pn
	inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId
	inner join vwProviderName p on p.PNNameId = pc.PCName2Id and p.ProviderPk = pc.ProviderPk
	where pc.PCRelationshipFk = 6 and pn.PNPk = @PNPk  


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Provider
	@providerPk int
	
AS

	select * from tblProvider
	where ProviderPk = @providerPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderConcept
	@PCPk int
AS

	select * 
	from vwProviderConcept
	where PCPk = @PCPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderConceptById
	@providerPk int,
	@PCConceptId nvarchar(300)
AS

	select *
	from vwProviderConcept
	where PCConceptId = @PCConceptId and ProviderPk = @providerPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderConcetps
	@nameGuid uniqueidentifier
AS

	select *
	from vwProviderConcept pc
	inner join tblConcept c on c.ConceptPk = pc.pcconceptfk
	where ConceptName1Fk = @nameGuid and PCLinkStatus <> 'Discarded'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderImports
	@providerPk int
AS

	select * from tblProviderImport
	where ProviderImportProviderFk = @providerPk
	order by ProviderImportDate desc


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderName
	@PNPk int
	
AS

	select *
	from vwProviderName
	where PNPk = @PNPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderNameById
	@providerPk int,
	@PNNameId nvarchar(300)
AS

	
	select *
	from vwProviderName
	where PNNameId = @PNNameId and ProviderPk = @providerPk
	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderNames
	@nameGuid uniqueidentifier
	
AS

	
	
	select *
	from vwProviderName
	where PNNameFk = @nameGuid and PNLinkStatus <> 'Discarded'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderRIS
	@PRISPk int
AS

	select * from tblProviderRIS
	where PRISPk = @PRISPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderRISById
	@PRISID nvarchar(300)
AS

	select * from tblProviderRIS
	where PRISID = @PRISID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderRISByReference
	@PRPk int
AS

	select *
	from tblProviderRIS
	where PRISProviderReferenceFk = @PRPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderRISByReferenceGuid
	@referenceGuid uniqueidentifier
AS

	select r.*
	from vwProviderRIS r
	where r.PRReferenceFk = @referenceGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderRISByReferenceId
	@ReferenceId nvarchar(300)
AS

	select * 
	from tblProviderRIS
	where PRISId = @referenceId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderReference
	@PRPk int
AS


	select *
	from vwProviderReference
	where PRPk = @PRPk 
	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderReferenceById
	@providerPk int,
	@PRReferenceId nvarchar(300)
AS

	select *
	from vwProviderReference
	where PRReferenceId = @PRReferenceId and ProviderPk = @providerPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProviderReferences
	@referenceGuid uniqueidentifier
AS

	select *
	from vwProviderReference
	where PRReferenceFk = @referenceGuid and PRLinkStatus <> 'Discarded'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Providers
	
AS

select * from tblProvider


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProvidersForName
	@nameGuid uniqueidentifier
AS

	select p.*
	from tblProvider p
	inner join (select distinct p.ProviderPk
		from tblProvider p
		inner join tblProviderImport pim on pim.ProviderImportProviderFk = p.ProviderPk
		inner join tblProviderName pn on pn.PNProviderImportFk = pim.ProviderImportPk
		where pn.PNNameFk = @nameGuid and pn.PNLinkStatus <> 'Discarded') ps on ps.ProviderPk = p.ProviderPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ProvidersForReference
	@referenceGuid uniqueidentifier
AS

	
	select p.*
	from tblProvider p
	inner join (select distinct p.ProviderPk
		from tblProvider p
		inner join tblProviderImport pim on pim.ProviderImportProviderFk = p.ProviderPk
		inner join tblProviderReference pr on pr.PRProviderImportFk = pim.ProviderImportPk
		where pr.PRReferenceFk = @referenceGuid and PRLinkStatus <> 'Discarded') ps on ps.ProviderPk = p.ProviderPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_RISByReference
	@refGuid uniqueidentifier
AS

	select *
	from tblReferenceRIS
	where RISReferenceFk = @refGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_RISMappings
	
AS

	select * from tblRISMapping


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Ranks
	
AS

	select * from tblRank


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Reference
	@referenceGuid uniqueidentifier
AS

	select cast(ReferenceGUID as nvarchar(38)) as ReferenceGuid,
		ReferenceLSID,
		ReferenceCitation,
		ReferenceFullCitation,
		ReferenceCreatedDate,
		ReferenceCreatedBy,
		ReferenceUpdatedDate,
		ReferenceUpdatedBy
	from tblReference
	where ReferenceGuid = @referenceGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ReferenceMatches
	@providerReferencePk int,
	@matchRuleSet int
AS

	delete tmpMatchResults
		
	
	--execute each match function until either no records in tmpMatchResults or end of match set
		
	declare @matchFuncs table(MatchPk int, MatchFunction nvarchar(1000), Threshold int, PassFk int)
	
	insert into @matchFuncs
	select MatchPk, MatchFunction, MatchThreshold, MatchPassFk
	from tblMatch
	where MatchRuleSet = @matchRuleSet
	order by MatchFirst desc
	
	declare @finished bit, @func nvarchar(1000), @pk int, @passFk int, @thHold int, @count int
	set @finished = 0
	select top 1 @pk = MatchPk from @matchFuncs
	
	while (@finished = 0)
	begin
		select @pk = MatchPk, @func = MatchFunction, @thHold = Threshold, @passFk = PassFk
		from @matchFuncs 
		where MatchPk = @pk
		
		--build up stored proc call
		declare @spCall nvarchar(4000)				
		set @spCall = 'exec ' + @func + ' ' + cast(@providerReferencePk as nvarchar(50)) + ', ' + cast(@thHold as varchar(10))
		--todo other params, are they required?
		print(@spCall)
		exec(@spCall)						
		
		select @count = count(*) from tmpMatchResults
		
		if (@count = 0) set @finished = 1				
		else set @pk = @passFk
		if (@pk is null) set @finished = 1
	end
	
	--return remaining matching refs
	select cast(ReferenceGUID as nvarchar(38)) as ReferenceGuid,
		ReferenceLSID,
		ReferenceCitation,
		ReferenceFullCitation,
		ReferenceCreatedDate,
		ReferenceCreatedBy,
		ReferenceUpdatedDate,
		ReferenceUpdatedBy
	from tblReference r
	inner join tmpMatchResults mr on mr.MatchResultRecordId = r.ReferenceGuid
	
	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ReferencesWithCitation
	@providerRefPk int,
	@threshold int
AS

	declare @refCitation nvarchar(4000)
	select @refCitation = lower(PRCitation) from tblProviderReference where PRPk = @providerRefPk
	

	if ((select count(*) from tmpMatchResults) = 0) --assume this is the first match
	begin
		insert tmpMatchResults
		select ReferenceGuid, dbo.fnLevenshteinPercentage(lower(ReferenceCitation), @refCitation)
		from tblReference
		where dbo.fnLevenshteinPercentage(lower(ReferenceCitation), @refCitation) >= @threshold --todo threshold, soundex?
	end
	else
	begin
		delete mr
		from tmpMatchResults mr
		inner join tblReference r on r.ReferenceGuid = mr.MatchResultRecordId
		where dbo.fnLevenshteinPercentage(lower(ReferenceCitation), @refCitation) < @threshold --todo threshold, soundex?
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_RelationshipTypes

AS

	select * 
	from tblRelationshipType
	where RelationshipTypeName is not null


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ReportConflictingData
	
AS
	
	declare @res table(recordId nvarchar(300), recordDetails nvarchar(3000), recordType nvarchar(50), TableName nvarchar(100))
	
	insert @res
	select cast(PNPk as varchar(300)), PNNameFull, 'ProviderName', 'tblProviderName'
	from vwProviderName pn
	inner join tblName n on n.NameGuid = pn.PNNameFk
	where PNLinkStatus <> 'Discarded' and 
		(isnull(PNNameFull, NameFull) collate SQL_Latin1_General_CP1_CI_AI <> NameFull or
		isnull(PNNameRank, NameRank) <> NameRank or
		isnull(PNNameCanonical, NameCanonical) <> NameCanonical or 
		isnull(PNNameAuthors, NameAuthors) <> NameAuthors or
		isnull(PNBasionymAuthors, NameBasionymAuthors) <> NameBasionymAuthors or
		isnull(PNCombinationAuthors, NameCombinationAuthors) <> NameCombinationAuthors or
		isnull(PNPublishedIn, NamePublishedIn) <> NamePublishedIn or
		isnull(PNYear, NameYear) <> NameYear or
		isnull(PNMicroReference, NameMicroReference) <> NameMicroReference or
		isnull(PNTypeVoucher, NameTypeVoucher) like NameTypeVoucher or
		isnull(PNTypeName, NameTypeName) <> NameTypeName or
		isnull(PNOrthography, NameOrthography) <> NameOrthography or
		isnull(PNBasionym, NameBasionym) <> NameBasionym or
		isnull(PNBasedOn, NameBasedOn) <> NameBasedOn or
		isnull(PNConservedAgainst, NameConservedAgainst) <> NameConservedAgainst or
		isnull(PNHomonymOf, NameHomonymOf) <> NameHomonymOf or
		isnull(PNReplacementFor, NameReplacementFor) <> NameReplacementFor or
		isnull(PNInvalid, NameInvalid) <> NameInvalid or
		isnull(PNIllegitimate, NameIllegitimate) <> NameIllegitimate or
		isnull(PNMisapplied, NameMisapplied) <> NameMisapplied or
		isnull(PNProParte, NameProParte) <> NameProParte) 

			
	insert @res
	select cast(PRPk as varchar(300)), isnull(PRCitation, PRFullCitation), 'ProviderReference', 'tblProviderReference'
	from vwProviderReference pr
	inner join vwProviderRIS pris on pris.PRISProviderReferenceFk = pr.PRPk
	inner join tblReferenceRIS ris on ris.RISPk = pris.PRISRISFk
	where PRLinkStatus <> 'Discarded' and 
		(isnull(PRISType, RISType) <> RISType or
		isnull(PRISAuthors, RISAuthors) <> RISAuthors or
		isnull(PRISTitle, RISTitle) like RISTitle or
		isnull(PRISDate, RISDate) <> RISDate or
		isnull(PRISNotes, RISNotes) like RISNotes or
		isnull(PRISKeywords, RISKeywords) <> RISKeywords or
		isnull(PRISStartPage, RISStartPage) <> RISStartPage or
		isnull(PRISEndPage, RISEndPage) <> RISEndPage or
		isnull(PRISJournalName, RISJournalName) <> RISJournalName or
		isnull(PRISStandardAbbreviation, RISStandardAbbreviation) <> RISStandardAbbreviation or
		isnull(PRISVolume, RISVolume) <> RISVolume or
		isnull(PRISIssue, RISIssue) <> RISIssue or
		isnull(PRISCityOfPublication, RISCityOfPublication) <> RISCityOfPublication or
		isnull(PRISPublisher, RISPublisher) <> RISPublisher or
		isnull(PRISISSNNumber, RISSNNumber) <> RISSNNumber or
		isnull(PRISWebUrl, RISWebUrl) <> RISWebUrl or 
		isnull(PRISTitle2, RISTitle2) like RISTitle2 or
		isnull(PRISTitle3, RISTitle3) like RISTitle3 or
		isnull(PRISAuthors2, RISAuthors2) <> RISAuthors2 or
		isnull(PRISAuthors3, RISAuthors3) <> RISAuthors3 )
	
	select r.*
	from @res r
	left join tblReportError re on re.ReportErrorRecordFk = r.RecordId and re.ReportErrorStatusFk = 3 and re.ReportErrorTable = r.TableName
	where re.ReportErrorPk is null
	order by recordType, recordId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ReportLowMatchScores
	
AS
	declare @minScore int, @maxScore int
	set @minScore = 75
	set @maxScore = 90

	declare @res table(recordId nvarchar(300), recordDetails nvarchar(3000), recordType nvarchar(50), TableName nvarchar(100))
	
	insert @res
	select cast(PNPk as varchar(300)), PNNameFull, 'ProviderName', 'tblProviderName'
	from vwProviderName
	where PNNameFk is not null and PNNameMatchScore < @maxScore and PNNameMatchScore > @minScore
			
	
	select r.*
	from @res r
	left join tblReportError re on re.ReportErrorRecordFk = r.RecordId and re.ReportErrorStatusFk = 3 and re.ReportErrorTable = r.TableName
	where re.ReportErrorPk is null
	order by recordType, recordId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ReportMultipleMatches
	
AS
	
	declare @res table(recordId nvarchar(300), recordDetails nvarchar(3000), recordType nvarchar(50), TableName nvarchar(100))
	
	insert @res
	select cast(PNPk as varchar(300)), PNNameFull, 'ProviderName', 'tblProviderName'
	from vwProviderName
	where PNNameFk is null and PNLinkStatus = 'Multiple'
			
	insert @res
	select cast(PRPk as varchar(300)), isnull(PRCitation, PRFullCitation), 'ProviderReference', 'tblProviderReference'
	from vwProviderReference
	where PRReferenceFk is null and PRLinkStatus = 'Multiple'
	
	select r.*
	from @res r
	left join tblReportError re on re.ReportErrorRecordFk = r.RecordId and re.ReportErrorStatusFk = 3 and re.ReportErrorTable = r.TableName
	where re.ReportErrorPk is null
	order by recordType, recordId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_ReportUnlinkedRecords

AS

	declare @res table(recordId nvarchar(300), recordDetails nvarchar(3000), recordType nvarchar(50), TableName nvarchar(100))
	
	insert @res
	select cast(PNPk as varchar(300)), PNNameFull, 'ProviderName', 'tblProviderName'
	from vwProviderName
	where PNNameFk is null and PNLinkStatus <> 'Discarded'
		
	insert @res
	select cast(PCPk as varchar(300)), PCName1 + ' ' + PCRelationship + ' ' + PCName2, 'ProviderConcept', 'tblProviderConcept'
	from vwProviderConcept
	where PCConceptFk is null 
	
	insert @res
	select cast(PRPk as varchar(300)), isnull(PRCitation, PRFullCitation), 'ProviderReference', 'tblProviderReference'
	from vwProviderReference
	where PRReferenceFk is null and PRLinkStatus <> 'Discarded'
	
	select r.*
	from @res r
	left join tblReportError re on re.ReportErrorRecordFk = r.RecordId and re.ReportErrorStatusFk = 3 and re.ReportErrorTable = r.TableName
	where re.ReportErrorPk is null
	order by recordType, recordId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Reports
AS

	select *
	from tblReport


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure dbo.sprSelect_RootKey
AS

/******************************************************************************
**		File: 
**		Name: sprSelect_RootKey
**		Desc: 
**
**		This template can be customized:
**              
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: 
**		Date: 
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/

select cast(NameGuid as varchar(38)) from tblName where NameFull = 'ROOT'



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_RunReport
	@reportPk int
AS

	declare @sql nvarchar(1000), @sp nvarchar(300)
	
	select @sp = ReportStoredProc
	from tblReport 
	where ReportPk = @reportPk
	
	set @sql = 'exec ' + @sp  --todo add params to restrict report data

	exec(@sql)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.sprSelect_SearchNames2
	@nvcSearchText1			nvarchar(100),			-- First Search Text
	@nvcField1				nvarchar(100),			-- First Field to be searched (field in tblName)
	@bitWholeWord1			bit = 0,				-- 1: search for Whole Word	0: any part of word	
	@bitAnywhere1			bit = 0,				-- 1: Anywhere in Field 0: Start of Field
	
	@bitUseOr				bit = 0,				-- 1: Search1 Or Search2   0: Search1 And Search2 
	
	@nvcSearchText2			nvarchar(100) = NULL,	-- Second Search Text, if null or empty string, only first search text is used
	@nvcField2				nvarchar(100) = NULL,	-- Second Field to be searched (field in tblName) 
	@bitWholeWord2			bit = 0,				-- 1: search for Whole Word	0: any part of word	
	@bitAnywhere2			bit = 0,				-- 1: Anywhere in Field 0: Start of Field
	
	@bitMisappliedOnly		bit = 0,				-- Filter search for only those that are misapplied
	@bitHybridOnly			bit = 0,				-- Filter search for only those that are hybrid
	@bitCurrentNamesOnly		bit = 0,			-- Filter search for only those that are CurrentNames
	
	@intRoleKey				int,					-- Role for read permissions: search results are filtered to show names that have read permission for the given role (Admin = 1 ie  everything)

	@nvcOrderBy1			nvarchar(100) = NULL,	-- First Order By Field  (Note: not all columns of name are return at present)
	@bitDescending1			bit = 0,				-- First Order 1: Descending 0: Ascending
	@nvcOrderBy2			nvarchar(100) = NULL,	-- Second Order By Field  (Note: not all columns of name are return at present)
	@bitDescending2			bit = 0,					-- Second Order 1: Descending 0: Ascending
	
	@intStartIndex			int = NULL,				-- for paging
	@intEndIndex			int = NULL,				-- for paging
	@intTotalRows			int = NULL OUTPUT,
	@bitDoNotShowSuppressed	bit = 1,
	
	@intYearStart			int = NULL,
	@intYearEnd				int = NULL,
	@uidCancelQuery			uniqueidentifier = NULL, 
	
	@FilterByTag			bit = 0
	
AS

	SET NOCOUNT ON

	DECLARE @nvcSqlQuery	varchar(8000)
	DECLARE @nvcSelect		varchar(3000)
	DECLARE @nvcFrom		varchar(2000)
	DECLARE @nvcWhereTrim	varchar(2000)
	
	DECLARE @nvcAndOr		varchar(10)
	
	DECLARE @nvcSqlQueryFinal	varchar(8000)
	DECLARE @nvcWhere		varchar(2000)
	DECLARE @nvcOrder		varchar(2000)
	
	IF @FilterByTag IS NULL
		SET @FilterByTag = 0
	
	
	IF @bitWholeWord1 IS NULL
		SET @bitWholeWord1 = 0
	
	IF @bitWholeWord2 IS NULL
		SET @bitWholeWord2 = 0
	
	IF @bitAnywhere1 IS NULL
		SET @bitAnywhere1 = 0
	
	IF @bitAnywhere2 IS NULL
		SET @bitAnywhere2 = 0
	
	IF @bitMisappliedOnly IS NULL
		SET @bitMisappliedOnly = 0
		
	IF @bitHybridOnly IS NULL
		SET @bitHybridOnly = 0
		
	IF @bitCurrentNamesOnly IS NULL
		SET @bitCurrentNamesOnly = 0
	
	IF @intEndIndex IS NULL
		SET @intEndIndex = 0
	
	IF @intStartIndex IS NULL
		SET @intStartIndex = 0
		
	IF @bitDescending1 IS NULL
		SET @bitDescending1 = 0
		
	IF @bitDescending2 IS NULL
		SET @bitDescending2 = 0
	
	
	-- the text for where
	IF @bitUseOr IS NULL OR @bitUseOr = 0
		SET @nvcAndOr = ' AND '
	ELSE
		SET @nvcAndOr = ' OR '
	
	-- **** Important: the search fields are all in tblName at present and not in the joined tables
	
	-- *************************** SELECT **********************************
	SET @nvcSelect = 'SELECT
		n1.NameCreatedBy as NameAddedBy,
		n1.NameCreatedDate as NameAddedDate,
		null as NameAggregate,
		null as NameAnamorphGenusFK,
		n1.NameAuthors, 
		0 as NameAutonym,
		cast(n1.NameBasedOnFK as varchar(38)) as NameBasedOnFk,
		cast(n1.NameBasionymFK as varchar(38)) as NameBasionymFk,
		null as NameBlockingFK,
		isnull(n1.NameCanonical, ''[unnamed]'') as NameCanonical,
		null as NameCheckStatus,
		null as NameClassificationFK,
		cast(n1.NamePreferredFk as varchar(38)) as NameCurrentFK,
		0 as NameDubium,
		isnull(n1.NameFull, '''') as NameFull,
		null as NameHybridLink,
		isnull(n1.NameIllegitimate, 0) as NameIllegitimate,
		0 as NameInCitation,
		isnull(n1.NameInvalid, 0) as NameInvalid,
		0 as NameIsAnamorph,
		isnull(n1.NameMisapplied, 0) as NameMisapplied,
		''ICBN'' as NameNomCode,
		0 as NameNovum,
		n1.NameOrthography as NameOrthographyVariant,
		null as NameOwner,
		null as NamePage,
		cast(n1.NameParentFk as varchar(38)) as NameParentFK,
		null as NamePrimaryOwnerFk,
		isnull(n1.NameProParte, 0) as NameProParte,
		cast(n1.NameReferenceFK as varchar(38)) as NameReferenceFk,
		null as NameSanctioningAuthor,
		null as NameSanctioningPage,
		null as NameSanctioningReferenceFK,
		0 as NameSuppress,
		null as NameTaxonomyReferenceFK,
		isnull(n1.NameRankFk, 1) as NameTaxonRankFk,
		null as NameTempRepKey,
		null as NameTypeLocality,
		cast(n1.NameTypeNameFk  as varchar(38))as NameTypeTaxonFK,
		n1.NameUpdatedBy,
		null as NameUpdatedByFK,
		n1.NameUpdatedDate,
		cast(n1.NameGuid as varchar(38)) as NameGuid,
		n1.NameYear as NameYearOfPublication, 
		null as NameYearOnPublication,  
		n1.NameParent as ParentName, 
		n1.NameBasionym as BasionymName, 
		null as BasionymDate,
		isnull(n1.NameLSID,'''') as NameLSID,
		tr.RankName as TaxonRankName, tr.RankAbbreviation as TaxonRankAbbreviation  '
			
			-- *************************** FROM ************************************
			SET @nvcFrom =  ' FROM tblName n1 
		LEFT JOIN tblRank tr ON n1.NameRankFk = tr.RankPk '
	
	-- *************************** WHERE TRIM ********************************
	-- this is used to pre-cut the set which is then pruned by the wholeword function
	
	DECLARE @nvcPrefix1 nvarchar(10)
	DECLARE @nvcPrefix2 nvarchar(10)
	SET @nvcPrefix1 = 'n1.'
	SET @nvcPrefix2 = 'n1.'
	IF @nvcField1 IS NOT NULL AND LEN(@nvcField1) > 4
		BEGIN
		IF  SUBSTRING(@nvcField1, 1, 4) <> 'Name'
			SET @nvcPrefix1 = ''
		END
	IF @nvcField2 IS NOT NULL AND LEN(@nvcField2) > 4
		BEGIN
		IF  SUBSTRING(@nvcField2, 1, 4) <> 'Name'
			SET @nvcPrefix2 = ''
		END
	
 
	
	IF @bitAnywhere1 = 0 OR @bitAnywhere1 IS NULL
		SET @nvcWhereTrim = ' WHERE ( ' + @nvcPrefix1 + @nvcField1 + ' LIKE ''' + @nvcSearchText1 + '%'' '
	ELSE
		SET @nvcWhereTrim = ' WHERE ( ' + @nvcPrefix1 + @nvcField1 + ' LIKE ''%' + @nvcSearchText1 + '%'' '
		
	IF NOT (@nvcSearchText2 IS NULL OR RTrim(@nvcSearchText2) = '')
		BEGIN
		IF @bitAnywhere2 = 0 OR @bitAnywhere2 IS NULL
			SET @nvcWhereTrim = @nvcWhereTrim + ' ' + @nvcAndOr + ' ' + @nvcPrefix2 +  @nvcField2 + ' LIKE ''' + @nvcSearchText2 + '%''' 
		ELSE
			SET @nvcWhereTrim = @nvcWhereTrim + ' ' + @nvcAndOr + ' ' + @nvcPrefix2 +  @nvcField2 + ' LIKE ''%' + @nvcSearchText2 + '%''' 
		END	
	
	SET @nvcWhereTrim = @nvcWhereTrim + ' ) '
		
	IF @bitMisappliedOnly = 1
		SET @nvcWhereTrim = @nvcWhereTrim + ' AND n1.NameMisapplied = 1 '

	--IF @bitHybridOnly = 1
      --  SET @nvcWhereTrim = @nvcWhereTrim + ' AND n1.NameHybridLink = ''F'' '
	
	--IF @bitCurrentNamesOnly = 1
      --SET @nvcWhereTrim = @nvcWhereTrim + ' AND n1.NameGuid = n1.NamePreferredNameFK '
	
	--IF @bitDoNotShowSuppressed = 1
      --SET @nvcWhereTrim = @nvcWhereTrim + ' AND n1.NameSuppress = 0 '
      
    --IF @FilterByTag = 1 
      --SET @nvcWhereTrim = @nvcWhereTrim + ' AND (NOT EXISTS (SELECT 1 FROM dbo.tblNomenclaturalStatus WHERE NomenclaturalStatusNameFK = n1.NameGuid AND NomenclaturalStatusStatusTypeFK = 6)) '
      
    --IF (@intYearStart IS NOT NULL AND @intYearStart > 0) OR (@intYearEnd IS NOT NULL AND @intYearEnd > 0)
	--	BEGIN
	--	IF @nvcWhereTrim <> '' 
	--		SET @nvcWhere = @nvcWhere + ' AND '
	--	
	--	SET @nvcWhereTrim = @nvcWhereTrim + ' dbo.IsYearInRange(NameYearOfPublication, NameYearOnPublication, cast(' +  cast(IsNull(@intYearStart, 0) as nvarchar(10)) + ' as int) , cast(' +  cast(IsNull(@intYearEnd,0) as nvarchar(10)) + ' as int) ) = 1 '
	--	END
      	
	-- *************************** ORDER BY *********************************
	SET @nvcOrder = ' ORDER BY '
	IF @nvcOrderBy1 IS NULL OR RTrim(@nvcOrderBy1) = '' -- Only add order if @nvcOrderBy1 contains a field
		BEGIN 
		SET @nvcOrder = @nvcOrder + ' n1.NameFull '
		END 
	ELSE
		BEGIN
		SET @nvcOrder = @nvcOrder + ' n1.' + @nvcOrderBy1
		
		IF @bitDescending1 = 1 -- Descending Flag
			SET @nvcOrder = @nvcOrder + ' DESC '
		
		IF NOT (@nvcOrderBy2 IS NULL OR RTrim(@nvcOrderBy2) = '') AND NOT @nvcOrderBy2 = @nvcOrderBy1 -- Only add second order if @nvcOrderBy2 contains a field
			BEGIN
			SET @nvcOrder = ' n1.' + @nvcOrder + ' , ' + ' n1.' + @nvcOrderBy2
			
			IF @bitDescending2 = 1 -- Descending Flag
				SET @nvcOrder = @nvcOrder + ' DESC '
			
			END
		END	
		
	
	--Quick execution if no paging and no wholeword selection
	IF @bitWholeWord1 = 0 AND @bitWholeWord1 = 0 AND @intStartIndex = 0 AND @intEndIndex = 0
		BEGIN
		SET @nvcSqlQuery = @nvcSelect + @nvcFrom + @nvcWhereTrim + @nvcOrder
		PRINT @nvcSqlQuery 
		EXEC(@nvcSqlQuery)
		RETURN @@ERROR
		END
	
	
	-- *************************** WHERE ************************************
	IF NOT (@nvcSearchText1 IS NULL OR RTrim(@nvcSearchText1) = '')
		BEGIN
		SET @nvcWhere = ' dbo.FindWord('''  + @nvcSearchText1 + ''', ' + @nvcPrefix1 + @nvcField1 + ', ' + cast(@bitAnywhere1 as nvarchar(100)) + ', ' + cast(@bitWholeWord1 as nvarchar(100)) + ') = 1' 
		IF NOT (@nvcSearchText2 IS NULL OR RTrim(@nvcSearchText2) = '')
			SET @nvcWhere = @nvcWhere + @nvcAndOr + ' dbo.FindWord('''  + @nvcSearchText2 + ''', ' + @nvcPrefix2 + @nvcField2 + ', ' + cast(@bitAnywhere2 as nvarchar(100)) + ', ' + cast(@bitWholeWord2 as nvarchar(100)) + ') = 1' 
		END
	ELSE
		BEGIN
		SET @nvcWhere = ''
		END
	
	--Quick execution if no paging 
	IF @intStartIndex = 0 AND @intEndIndex = 0
		BEGIN
		SET @nvcSqlQuery = @nvcSelect + @nvcFrom + @nvcWhereTrim + ' AND (' + @nvcWhere + ')' + @nvcOrder
		PRINT @nvcSqlQuery 
		PRINT @nvcWhereTrim
		PRINT @nvcWhere 
		EXEC(@nvcSqlQuery)
		RETURN @@ERROR
		END
		
	-- **************************** EXECUTE *********************************
	-- paging is done by sending the top <end index> results into temp table and selecting the top <end index - start index> in the opersite order
	
	DECLARE @sp_id uniqueidentifier
	SET @sp_id = NEWID() -- the id of this instance of this stored procedure
	
	

	
	-- insert into temp table, reverse order
	SET @nvcSqlQuery = ' INSERT INTO tmpNameSearch(NameSearchSP_Key, NameSearchOrderByTimestamp, NameSearchNameFk, NameSearchName)  
		SELECT TOP ' 
			+ CAST(@intEndIndex AS nvarchar(20)) 
			+ ' ''' + CAST(@sp_id AS nvarchar(100)) 
			+ ''', NULL, n1.NameGuid, n1.NameFull FROM tblName n1 LEFT JOIN tblRank tr ON n1.NameRankFk = tr.RankPk '  
			+ @nvcWhereTrim + ' AND (' + @nvcWhere + ')' + @nvcOrder
	--print @nvcSqlQuery
	EXEC(@nvcSqlQuery)
	
	-- bail if cancel has been requested
	--IF dbo.IsCancelRequested(@uidCancelQuery) = 1
	--	BEGIN
	--	exec z_sprInsert_DebugEntry 'sprSelect_SearchNames', 'Cancel', '2'
	--	EXEC sprUpdate_CancelRequestClear @uidCancelQuery
	--	DELETE FROM tmpNameSearch WHERE  NameSearchSP_Key = @sp_id
	--	RETURN @@ERROR
	--	END
	
	-- Get total 
	SET @nvcSqlQuery = ' INSERT INTO tmpSearchCount(SearchCountCount, SearchCountTimestamp, SearchCountSP_Key) 
		SELECT COUNT(*), NULL, ''' 
			+ CAST(@sp_id AS nvarchar(100)) 
			+ ''' FROM tblName n1 LEFT JOIN tblTaxonRank tr ON n1.NameTaxonRankFk = tr.TaxonRankPk ' 
			+ @nvcWhereTrim 
			+ ' AND (' + @nvcWhere + ')'
	EXEC(@nvcSqlQuery)	
	SELECT @intTotalRows = SearchCountCount FROM tmpSearchCount
	
	
	
	-- set start index
	DECLARE @intAdjustedStartIndex int
	SET @intAdjustedStartIndex = @intEndIndex + 1 - @intStartIndex
	IF @intTotalRows < @intEndIndex
		BEGIN
		SET @intAdjustedStartIndex = @intAdjustedStartIndex - (@intEndIndex - @intTotalRows)
		END
	
	DECLARE @sp_id_Reverse uniqueidentifier
	SET @sp_id_Reverse = NEWID() -- the id of this instance of this stored procedure for the reverse
	
	--SELECT @sp_id
	
	-- bail if cancel has been requested
	--IF dbo.IsCancelRequested(@uidCancelQuery) = 1
	--	BEGIN
	--	exec z_sprInsert_DebugEntry 'sprSelect_SearchNames', 'Cancel', '3'
	--	EXEC sprUpdate_CancelRequestClear @uidCancelQuery
	--	DELETE FROM tmpNameSearch WHERE  NameSearchSP_Key = @sp_id_Reverse OR NameSearchSP_Key = @sp_id
	--	RETURN @@ERROR
	--	END
	
	--reverse order
	SET @nvcSqlQuery = 
	'   INSERT INTO tmpNameSearch(NameSearchSP_Key, NameSearchOrderByTimestamp, NameSearchNameFk, NameSearchName)  
		SELECT TOP ' + CAST(@intAdjustedStartIndex AS nvarchar(20)) 
		+ ' ''' +  CAST(@sp_id_Reverse AS nvarchar(100)) +  ''', NULL, n2.NameSearchNameFk, n2.NameSearchName
		FROM tmpNameSearch n2
		WHERE n2.NameSearchSP_Key = ''' + CAST(@sp_id AS nvarchar(100)) + '''
		ORDER BY n2.NameSearchOrderByTimestamp DESC '
	print @nvcSqlQuery
	EXEC(@nvcSqlQuery)
	
	
	SELECT n1.NameCreatedBy as NameAddedBy,
		n1.NameCreatedDate as NameAddedDate,
		null as NameAggregate,
		null as NameAnamorphGenusFK,
		n1.NameAuthors, 
		0 as NameAutonym,
		cast(n1.NameBasedOnFK as varchar(38)) as NameBasedOnFk,
		cast(n1.NameBasionymFK as varchar(38)) as NameBasionymFk,
		null as NameBlockingFK,
		isnull(n1.NameCanonical, '[unnamed]') as NameCanonical,
		null as NameCheckStatus,
		null as NameClassificationFK,
		cast(n1.NamePreferredFk as varchar(38)) as NameCurrentFK,
		0 as NameDubium,
		isnull(n1.NameFull, '') as NameFull,
		null as NameHybridLink,
		isnull(n1.NameIllegitimate, 0) as NameIllegitimate,
		0 as NameInCitation,
		isnull(n1.NameInvalid, 0) as NameInvalid,
		0 as NameIsAnamorph,
		isnull(n1.NameMisapplied, 0) as NameMisapplied,
		'ICBN' as NameNomCode,
		0 as NameNovum,
		n1.NameOrthography as NameOrthographyVariant,
		null as NameOwner,
		null as NamePage,
		cast(n1.NameParentFk as varchar(38)) as NameParentFK,
		null as NamePrimaryOwnerFk,
		isnull(n1.NameProParte, 0) as NameProParte,
		cast(n1.NameReferenceFK as varchar(38)) as NameReferenceFk,
		null as NameSanctioningAuthor,
		null as NameSanctioningPage,
		null as NameSanctioningReferenceFK,
		0 as NameSuppress,
		null as NameTaxonomyReferenceFK,
		isnull(n1.NameRankFk, 1) as NameTaxonRankFk,
		null as NameTempRepKey,
		null as NameTypeLocality,
		cast(n1.NameTypeNameFk  as varchar(38))as NameTypeTaxonFK,
		n1.NameUpdatedBy,
		null as NameUpdatedByFK,
		n1.NameUpdatedDate,
		cast(n1.NameGuid as varchar(38)) as NameGuid,
		n1.NameYear as NameYearOfPublication, 
		null as NameYearOnPublication,  
		n1.NameParent as ParentName, 
		n1.NameBasionym as BasionymName, 
		null as BasionymDate,
		isnull(n1.NameLSID,'') as NameLSID,
		tr.RankName as TaxonRankName, tr.RankAbbreviation as TaxonRankAbbreviation			
	FROM tmpNameSearch ns
		LEFT JOIN tblName n1 ON n1.NameGuid = ns.NameSearchNameFk
		LEFT JOIN tblRank tr ON n1.NameRankFk = tr.RankPk 
	WHERE NameSearchSP_Key = @sp_id_Reverse
	--AND (@FilterByTag = 0 OR NOT EXISTS (SELECT 1 FROM dbo.tblNomenclaturalStatus WHERE NomenclaturalStatusNameFK = n1.NameGuid AND NomenclaturalStatusStatusTypeFK = 6))
	ORDER BY NameSearchOrderByTimestamp DESC
	

		
	DELETE FROM tmpNameSearch WHERE  NameSearchSP_Key = @sp_id_Reverse OR NameSearchSP_Key = @sp_id
	
	DELETE FROM tmpSearchCount WHERE SearchCountSP_Key = @sp_id
	
	RETURN @@ERROR
	
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_SearchProviderNames
	@providerPk int,
	@linkType nvarchar(10),
	@filterText nvarchar(100),
	@maxRecords int
AS

	declare @filterLen int
	set @filterText = lower(ltrim(rtrim(@filterText)))
	set @filterLen = len(@filterText)
	
	if (@filterLen = 0) set @filterText = null
	if (@maxRecords is null) set @maxRecords = 200
		
	declare @recs table(PNPk int, counter int identity)
	insert @recs
	select PNPk 
	from tblProviderName pn
	left join tblProviderImport pim on pim.ProviderImportPk = pn.PNProviderImportFk
	left join tblProvider p on p.ProviderPk = pim.ProviderImportProviderFk
	where (@providerPk is null or p.ProviderPk = @providerPk) and
		(@linkType = 'any' or pn.PNNameFk is null) and
		(@filterText is null or lower(substring(PNNameFull, 1, @filterLen)) = @filterText) and
		PNLinkStatus <> 'Discarded'
	
	delete @recs 	
	where counter > @maxRecords
	
	
	select *
	from vwProviderName pn
	inner join @recs r on r.PNPk = pn.PNPk
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_SearchProviderRefs
	@providerPk int,
	@linkType nvarchar(10),
	@filterText nvarchar(100),
	@maxRecords int
AS

	declare @filterLen int
	set @filterText = lower(ltrim(rtrim(@filterText)))
	set @filterLen = len(@filterText)
	
	if (@filterLen = 0) set @filterText = null
	if (@maxRecords is null) set @maxRecords = 200
		
	declare @recs table(PRPk int, counter int identity)
	insert @recs
	select PRPk 
	from tblProviderReference pr
	left join tblProviderImport pim on pim.ProviderImportPk = pr.PRProviderImportFk
	left join tblProvider p on p.ProviderPk = pim.ProviderImportProviderFk
	where (@providerPk is null or p.ProviderPk = @providerPk) and
		(@linkType = 'any' or pr.PRReferenceFk is null) and
		(@filterText is null or lower(substring(PRFullCitation, 1, @filterLen)) = @filterText) and
		PRLinkStatus <> 'Discarded'
	
	delete @recs 	
	where counter > @maxRecords
	
	
	select *
	from vwProviderReference pr
	inner join @recs r on r.PRPk = pr.PRPk
	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_SystemProviderConcept
	@sysProvImportFk int,
	@name1Guid uniqueidentifier,
	@relTypeFk int
AS

	select *
	from vwProviderConcept pc
	inner join vwProviderName pn1 on pn1.PNNameId = pc.PCName1Id and pn1.ProviderPk = pc.ProviderPk
	where PCProviderImportFk = @sysProvImportFk and 
		pn1.PNNameFk = @name1Guid and
		PCRelationshipFk = @relTypeFk
		


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE Procedure sprSelect_SystemProviderNameForName
	@nameGuid uniqueidentifier
AS

	select *
	from vwProviderName v
	where ProviderIsEditor = 1 and v.PNNameFk = @nameGuid and PNLinkStatus <> 'Discarded'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_SystemProviderRefForRef
	@refGuid uniqueidentifier
AS

	
	select p.ProviderPk,
		p.ProviderName,
		p.ProviderIsEditor,
		PRPk,
		PRProviderImportFk,
		PRReferenceId,
		cast(PRReferenceFk as varchar(38)) as PRReferenceFk,
		PRLinkStatus,
		PRCitation,
		PRFullCitation,
		PRXML,
		PRCreatedDate,
		PRCreatedBy,
		PRUpdatedDate,
		PRUpdatedBy
	from tblProviderReference pr
	left join tblProviderImport pim on pim.ProviderImportPk = pr.PRProviderImportFk
	left join tblProvider p on p.ProviderPk = pim.ProviderImportProviderFk
	where PRReferenceFk = @refGuid and p.ProviderIsEditor = 1 and PRLinkStatus <> 'Discarded'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_UknownTaxaNode

AS

	select *
	from tblName
	where NameFull = 'Unknown'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_UnlinkedConcepts
	
AS

	select  *
	from vwProviderConcept pc
	left join vwProviderName pn on pn.PNNameId = PCName1Id and pn.ProviderPk = pc.ProviderPk
	left join tblRank r on r.RankPk = pn.PNNameRankFk 
	where PCConceptFk is null and PCLinkStatus <> 'Discarded'
	order by r.RankSort


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_UnlinkedNames
	
AS
	
	--assumes ranks have been linked up
	select *
	from vwProviderName
	inner join tblRank r on r.RankPk = PNNameRankFk
	where PNNameFk is null and PNLinkStatus <> 'Discarded'
	order by r.RankSort


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_UnlinkedReferences
	
AS

	select  *
	from vwProviderReference
	where PRReferenceFk is null and PRLinkStatus <> 'Discarded'
	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_UserProviders
	@userPk int
AS

	select up.* 
	from tblUserProvider up
	inner join tblProvider p on p.ProviderPk = up.UserProviderProviderFk
	where UserProviderUserFk = @userPk
	order by p.ProviderCreatedDate desc


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprSelect_Users
	
AS

	select * from tblUser

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ConceptData
	@conceptPk int,
	@user nvarchar(50)
AS

	--get source records 
	--if a compositae/system record exists then use any non-null fields to populate name data
	--otherwise populate from majority value for each field
	--todo what if equal number of majority values, eg 2 of each value?

	declare @cols table(row int identity, source nvarchar(100), dest nvarchar(100))
	declare @nameFk uniqueidentifier, @refFk uniqueidentifier
	
	insert @cols
	select ConceptMappingSourceCol, ConceptMappingDestCol
	from tblConceptMapping
	
	declare @row int, @count int, @sql nvarchar(1000), @source nvarchar(100), @dest nvarchar(100), @type sysname
	select @row = 1, @count = count(*) from @cols
	
	create table #vals(val nvarchar(4000), pk int, isSystem bit)
			
	while (@row < @count + 1)
	begin		
		select @source = source, @dest = dest from @cols where row = @row
		
		select @type = t.name 
		from systypes t 
		inner join syscolumns c on c.xtype = t.xtype and t.status = c.typestat
		inner join sysobjects o on o.id = c.id
		where c.name = @source and o.name = 'tblProviderConcept'
		
		delete #vals
		set @sql = 'insert #vals select cast(' + @source + ' as nvarchar(4000)), PCPk, isnull(ProviderIsEditor,0) '
		set @sql = @sql + ' from tblProviderConcept pc '
		set @sql = @sql + ' inner join tblProviderImport pim on pim.ProviderImportPk = pc.PCProviderImportFk '
		set @sql = @sql + ' inner join tblProvider p on p.ProviderPk = pim.ProviderImportProviderFk '
		set @sql = @sql + ' where PCConceptFk = ' + cast(@conceptPk as varchar(15)) + ' and ' + @source + ' is not null'			
		set @sql = @sql + ' and PCLinkStatus <> ''Discarded'''
		exec(@sql)
				
		if (exists(select * from #vals where isSystem = 1))
		begin
			--checklist system entered value
			if (@source = 'PCName2Id')
			begin
				--special case for id
				select top 1 @nameFk = PNNameFk 
				from vwProviderName pn
				inner join vwProviderConcept pc on pc.PCName2Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
				inner join #vals v on v.pk = pc.pcpk
				where pc.PCConceptFk = @conceptPk and v.isSystem = 1
				
				update tblConcept 
				set ConceptName2Fk = @nameFk, ConceptUpdatedBy = @user, ConceptUpdatedDate = getdate()
				where ConceptPk = @conceptPk
			end
			else if (@source = 'PCAccordingToId')
			begin
				--special case for ref id
				select top 1 @refFk = PRReferenceFk 
				from tblProviderReference pr
				inner join tblProviderConcept pc on pc.PCAccordingToId = pr.PRReferenceId
				inner join #vals v on v.pk = pc.pcpk
				where pc.PCConceptFk = @conceptPk and v.isSystem = 1
				
				update tblConcept 
				set ConceptAccordingToFk = @refFk, ConceptUpdatedBy = @user, ConceptUpdatedDate = getdate() 
				where ConceptPk = @conceptPk
			end
			else if (@type = 'ntext')
			begin			
				--special case for text type fields
				set @sql = 'update c set ConceptUpdatedDate = getdate(), ConceptUpdatedBy = ''' + @user + ''', '
				set @sql = @sql + @dest + ' = pc.' + @source
				set @sql = @sql + ' from tblConcept c '
				set @sql = @sql + ' inner join tblProviderConcept pc on pc.PCConceptFk = c.ConceptPk '
				set @sql = @sql + ' inner join (select top 1 v.pk '
				set @sql = @sql + '   from #vals v where v.val is not null and v.isSystem = 1 '
				set @sql = @sql + '   group by v.pk ' 
				set @sql = @sql + '   order by count(*) desc) f on f.Pk = pn.PNPk '
				set @sql = @sql + ' where ConceptPk = ' + cast(@conceptPk as varchar(15)) 
			end 
			else
			begin
				set @sql = 'update tblConcept set ' + @dest + ' = (select top 1 pc.' + @source 
				set @sql = @sql + ' from tblProviderConcept pc inner join #vals v on v.pk = pc.PCPk where v.isSystem = 1) '
				set @sql = @sql + ', ConceptUpdatedBy = ''' + @user + ''', ConceptUpdatedDate = getdate() '
				set @sql = @sql + ' where ConceptPk = ' + cast(@ConceptPk as varchar(15)) 
			end
		end
		else
		begin
			--find most common value
			if (@source = 'PCName2Id')
			begin
				--special case for id
				select @nameFk = f.PNNameFk
				from (select top 1 PNNameFk, count(*) as c
					from vwProviderName pn
					inner join vwProviderConcept pc on pc.PCName2Id = pn.PNNameId and pn.ProviderPk = pc.ProviderPk
					inner join #vals v on v.pk = pc.pcpk
					where pc.PCConceptFk = @conceptPk 
					group by pc.PCAccordingToId, pn.PNNameFk
					order by c desc) f 
								
				update tblConcept 
				set ConceptName2Fk = @nameFk, ConceptUpdatedBy = @user, ConceptUpdatedDate = getdate()
				where ConceptPk = @conceptPk
			end
			else if (@source = 'PCAccordingToId')
			begin
				--special case for ref id
				select @refFk = f.PRReferenceFk
				from (select top 1 PRReferenceFk, count(*) as c
					from vwProviderReference pr
					inner join vwProviderConcept pc on pc.PCAccordingToId = pr.PRReferenceId and pr.ProviderPk = pc.ProviderPk
					inner join #vals v on v.pk = pc.pcpk
					where pc.PCConceptFk = @conceptPk 
					group by pc.PCAccordingToId, pr.PRReferenceFk
					order by c desc) f 
				
				update tblConcept 
				set ConceptAccordingToFk = @refFk, ConceptUpdatedBy = @user, ConceptUpdatedDate = getdate() 
				where ConceptPk = @conceptPk
			end
			else if (@type = 'ntext')
			begin			
				--special case for text type fields
				set @sql = 'update c set ConceptUpdatedDate = getdate(), ConceptUpdatedBy = ''' + @user + ''', '
				set @sql = @sql + @dest + ' = pc.' + @source
				set @sql = @sql + ' from tblConcept c '
				set @sql = @sql + ' inner join tblProviderConcept pc on pc.PCConceptFk = c.ConceptPk '
				set @sql = @sql + ' inner join (select top 1 v.pk '
				set @sql = @sql + '   from #vals v where v.val is not null '
				set @sql = @sql + '   group by v.val, v.pk ' 
				set @sql = @sql + '   order by count(*) desc) f on f.Pk = pn.PNPk '
				set @sql = @sql + ' where ConceptPk = ' + cast(@conceptPk as varchar(15)) 
				
			end 
			else
			begin		
				set @sql = 'update ct set ' + @dest + ' = f.' + @source + ', ConceptUpdatedDate = getdate(), ConceptUpdatedBy = ''' + @user + ''' '
				set @sql = @sql + ' from tblConcept ct '
				set @sql = @sql + ' inner join (select top 1 ' + @source + ', count(*) as c, PCConceptFk '
				set @sql = @sql + '      from tblProviderConcept '
				set @sql = @sql + '      where PCConceptFk = ' + cast(@conceptPk as varchar(15)) 
				set @sql = @sql + '      group by ' + @source + ', PCConceptFk '
				set @sql = @sql + '      order by c desc) f on f.PCConceptFk = ct.ConceptPk '
				set @sql = @sql + ' where ct.ConceptPk = ' + cast(@conceptPk as varchar(15)) 
			end
			    				
		end
		
		print(@type)
		print(@sql)
		exec(@sql)
		
		set @row = @row + 1
	end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ConceptLinks
	@nameGuid uniqueidentifier,
	@user nvarchar(50)
AS

	--update concept name links to point to the correct names
	--this will be done after a provider name has been relinked, so the concepts need to follow the relink

	declare @updates table(counter int identity, nameGuid uniqueidentifier)
	
	insert @updates
	select distinct ConceptName2Fk 
	from  tblConcept c
	inner join vwProviderConcept pc on pc.PCConceptFk = c.ConceptPk
	inner join vwProviderName pn on pn.PNNameId = pc.PCName1Id and pn.ProviderPk = pc.ProviderPk
	where c.ConceptName1Fk = @nameGuid
	
	insert @updates
	select distinct ConceptName1Fk 
	from  tblConcept c
	inner join vwProviderConcept pc on pc.PCConceptFk = c.ConceptPk
	inner join vwProviderName pn on pn.PNNameId = pc.PCName2Id and pn.ProviderPk = pc.ProviderPk
	where c.ConceptName2Fk = @nameGuid and not exists(select * from @updates where nameGuid = c.ConceptName1Fk)
	
	
	--do updates 
	
	update c
	set ConceptName1Fk = pn.PNNameFk,
		ConceptUpdatedBy = @user,
		ConceptUpdatedDate = getdate()
	from tblConcept c
	inner join vwProviderConcept pc on pc.PCConceptFk = c.ConceptPk
	inner join vwProviderName pn on pn.PNNameId = pc.PCName1Id and pn.ProviderPk = pc.ProviderPk
	where c.ConceptName1Fk = @nameGuid
	
	update c
	set ConceptName2Fk = pn.PNNameFk,
		ConceptUpdatedBy = @user,
		ConceptUpdatedDate = getdate()
	from tblConcept c
	inner join vwProviderConcept pc on pc.PCConceptFk = c.ConceptPk
	inner join vwProviderName pn on pn.PNNameId = pc.PCName2Id and pn.ProviderPk = pc.ProviderPk
	where c.ConceptName2Fk = @nameGuid


	--refresh name data for all affected names
	declare @pos int, @count int, @id uniqueidentifier
	select @pos = 1, @count = count(*)
	from @updates
	
	while (@pos <= @count)
	begin
		select @id = nameGuid from @updates where counter = @pos
		
		exec sprUpdate_NameData @id, @user
	
		set @pos = @pos + 1
	end
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_LinkedProviderNames
	@fromNameGuid uniqueidentifier,
	@toNameGuid uniqueidentifier,
	@linkStatus nvarchar(20),
	@user nvarchar(50)
AS

	--insert change records
	declare @pns table(PNPk int, counter int identity)
	insert @pns
	select PNPk 
	from tblProviderName
	where PNNameFk = @fromNameGuid and PNLinkStatus <> 'Discarded'
	
	declare @pos int, @count int, @pk int
	select @pos = 1, @count = count(*) from @pns
	while (@pos <= @count)
	begin
		select @pk = PNPk from @pns where counter = @pos
		exec sprInsert_ProviderNameChange @pk, @user
		set @pos = @pos + 1
	end

	update tblProviderName
	set PNNameFk = @toNameGuid,
		PNLinkStatus = @linkStatus,
		PNUpdatedDate = getdate(),
		PNUpdatedBy = @user	
	where PNNameFk = @fromNameGuid and PNLinkStatus <> 'Discarded'
	
	--update concepts for this name to point to the new consensus name
	
	update tblConcept
	set ConceptName1Fk = @toNameGuid,
		ConceptUpdatedBy = @user,
		ConceptUpdatedDate = getdate()
	where ConceptName1Fk = @fromNameGuid
	
	update tblConcept
	set ConceptName2Fk = @toNameGuid,
		ConceptUpdatedBy = @user,
		ConceptUpdatedDate = getdate()
	where ConceptName2Fk = @fromNameGuid


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_LinkedProviderReferences
	@fromRefGuid uniqueidentifier,
	@toRefGuid uniqueidentifier,
	@linkStatus nvarchar(20),
	@user nvarchar(50)
AS

	--insert change records
	declare @prs table(PRPk int, counter int identity)
	insert @prs
	select PRPk 
	from tblProviderReference
	where PRReferenceFk = @fromRefGuid and PRLinkStatus <> 'Discarded'
	
	declare @pos int, @count int, @pk int
	select @pos = 1, @count = count(*) from @prs
	while (@pos <= @count)
	begin
		select @pk = PRPk from @prs where counter = @pos
		exec sprInsert_ProviderReferenceChange @pk, @user
		set @pos = @pos + 1
	end

	update tblProviderReference
	set PRReferenceFk = @toRefGuid,
		PRLinkStatus = @linkStatus,
		PRUpdatedDate = getdate(),
		PRUpdatedBy = @user	
	where PRReferenceFk = @fromRefGuid and PRLinkStatus <> 'Discarded'
	
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_Name
	@NameGUID uniqueidentifier, 
	@NameLSID nvarchar(300), 
	@NameFull nvarchar(300), 
	@NameRank nvarchar(50), 
	@NameRankFk int, 
	@NameParentFk uniqueidentifier,
	@NameParentName nvarchar(300),
	@NamePreferredFk uniqueidentifier,
	@NamePreferredName nvarchar(300),
	@NameCanonical nvarchar(300), 
	@NameAuthors nvarchar(300), 
	@NameBasionymAuthors nvarchar(300), 
	@NameCombinationAuthors nvarchar(300), 
	@NamePublishedIn nvarchar(1000), 
	@NameReferenceFk uniqueidentifier, 
	@NameYear nvarchar(20), 
	@NameMicroReference nvarchar(150), 
	@NameTypeVoucher ntext, 
	@NameTypeName nvarchar(300), 
	@NameTypeNameFk uniqueidentifier, 
	@NameOrthography nvarchar(300), 
	@NameBasionym nvarchar(300), 
	@NameBasionymFk uniqueidentifier, 
	@NameBasedOn nvarchar(300), 
	@NameBasedOnFk uniqueidentifier, 
	@NameConservedAgainst nvarchar(300), 
	@NameConservedAgainstFk uniqueidentifier, 
	@NameHomonymOf nvarchar(300), 
	@NameHomonymOfFk uniqueidentifier, 
	@NameReplacementFor nvarchar(300), 
	@NameReplacementForFk uniqueidentifier, 
	@NameInvalid bit, 
	@NameIllegitimate bit, 
	@NameMisapplied bit, 
	@NameProParte bit, 
	@NameCounter int,
	@user nvarchar(50)
AS
	
	update tblName
	set NameLSID = @NameLSID, 
		NameFull = @NameFull, 
		NameRank = @NameRank, 
		NameRankFk = @NameRankFk, 
		NameParentFk = @NameParentFk,
		NameParent = @NameParentName,
		NamePreferredFk = @NamePreferredFk,
		NamePreferred = @NamePreferredName,
		NameCanonical = @NameCanonical, 
		NameAuthors = @NameAuthors, 
		NameBasionymAuthors = @NameBasionymAuthors, 
		NameCombinationAuthors = @NameCombinationAuthors, 
		NamePublishedIn = @NamePublishedIn, 
		NameReferenceFk = @NameReferenceFk, 
		NameYear = @NameYear, 
		NameMicroReference = @NameMicroReference, 
		NameTypeVoucher = @NameTypeVoucher, 
		NameTypeName = @NameTypeName, 
		NameTypeNameFk = @NameTypeNameFk, 
		NameOrthography = @NameOrthography, 
		NameBasionym = @NameBasionym, 
		NameBasionymFk = @NameBasionymFk, 
		NameBasedOn = @NameBasedOn, 
		NameBasedOnFk = @NameBasedOnFk, 
		NameConservedAgainst = @NameConservedAgainst, 
		NameConservedAgainstFk = @NameConservedAgainstFk, 
		NameHomonymOf = @NameHomonymOf, 
		NameHomonymOfFk = @NameHomonymOfFk, 
		NameReplacementFor = @NameReplacementFor, 
		NameReplacementForFk = @NameReplacementForFk, 
		NameInvalid = @NameInvalid, 
		NameIllegitimate = @NameIllegitimate, 
		NameMisapplied = @NameMisapplied, 
		NameProParte = @NameProParte, 
		NameUpdatedDate = getdate(), 
		NameUpdatedBy = @user,
		NameCounter = @NameCounter
	where NameGuid = @NameGuid

	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_NameData
	@nameGuid uniqueidentifier,
	@user nvarchar(50)
AS

	--get source records 
	--if a compositae/system record exists then use any non-null fields to populate name data
	--otherwise populate from majority value for each field
	--if equal number of majority values, eg 2 of each value, then update to null

	declare @cols table(row int identity, source nvarchar(100), dest nvarchar(100))
	
	insert @cols
	select NameMappingSourceCol, NameMappingDestCol
	from tblNameMapping
	where NameMappingSourceCol <> 'PNParentNameFk' --dont update parent as this upsets the tree
	
	declare @row int, @count int, @sql nvarchar(1000), @source nvarchar(100), @dest nvarchar(100), @type sysname
	select @row = 1, @count = count(*) from @cols
	
	declare @first int, @second int
	create table #vals(val nvarchar(4000), pk int, isSystem bit)
					
	while (@row < @count + 1)
	begin		
		select @source = source, @dest = dest from @cols where row = @row
		
		select @type = t.name 
		from systypes t 
		inner join syscolumns c on c.xtype = t.xtype and t.status = c.typestat
		inner join sysobjects o on o.id = c.id
		where c.name = @source and o.name = 'tblProviderName'
		
		delete #vals
		set @sql = 'insert #vals select cast(' + @source + ' as nvarchar(4000)), PNPk, isnull(ProviderIsEditor,0) '
		set @sql = @sql + ' from tblProviderName pn '
		set @sql = @sql + ' inner join tblProviderImport pim on pim.ProviderImportPk = pn.PNProviderImportFk '
		set @sql = @sql + ' inner join tblProvider p on p.ProviderPk = pim.ProviderImportProviderFk '
		set @sql = @sql + ' where PNNameFk = ''' + cast(@nameGuid as varchar(38)) + ''' and ' + @source + ' is not null'			
		set @sql = @sql + ' and PNLinkStatus <> ''Discarded'''
		exec(@sql)
				
		if (exists(select * from #vals where isSystem = 1))
		begin
			--checklist system entered value
			if (@type = 'ntext')
			begin			
				--special case for text type fields
				set @sql = 'update n set NameUpdatedDate = getdate(), NameUpdatedBy = ''' + @user + ''', '
				set @sql = @sql + @dest + ' = pn.' + @source
				set @sql = @sql + ' from tblName n '
				set @sql = @sql + ' inner join tblProviderName pn on pn.PNNameFk = n.NameGuid '
				set @sql = @sql + ' inner join (select top 1 v.pk '
				set @sql = @sql + '   from #vals v where v.val is not null and v.isSystem = 1 '
				set @sql = @sql + '   group by v.pk ' 
				set @sql = @sql + '   order by count(*) desc) f on f.Pk = pn.PNPk '
				set @sql = @sql + ' where NameGuid = ''' + cast(@nameGuid as varchar(38)) + ''''
			end 
			else
			begin
				set @sql = 'update tblName set ' + @dest + ' = (select top 1 pn.' + @source 
				set @sql = @sql + ' from tblProviderName pn inner join #vals v on v.pk = pn.PNPk where v.isSystem = 1) '
				set @sql = @sql + ', NameUpdatedBy = ''' + @user + ''', NameUpdatedDate = getdate() '
				set @sql = @sql + ' where NameGuid = ''' + cast(@nameGuid as varchar(38)) + ''''
			end
		end
		else
		begin
			create table #top2(counter int identity(1,1), val nvarchar(4000), number int)
			
			--find most common value
			if (@type = 'ntext')
			begin			
				--special case for text type fields
				set @sql = 'insert into #top2(val, number) '
				set @sql = @sql + 'select top 2 val, count(*) from #vals where val is not null '
				set @sql = @sql + ' group by val order by count(*) desc '
				
				exec(@sql)
				
				set @first = null
				set @second = null				
				select @first = number from #top2 where counter = 1
				select @second = number from #top2 where counter = 2
				
				if (@second is null or @first > @second or @source = 'PNNameCanonical') set @sql ='update n set ' + @dest + ' = pn.' + @source + ', NameUpdatedDate = getdate(), NameUpdatedBy = ''' + @user + ''' '
				else set @sql = 'update n set ' + @dest + ' = null, NameUpdatedDate = getdate(), NameUpdatedBy = ''' + @user + ''' '
				
				set @sql = @sql + ' from tblName n '
				set @sql = @sql + ' inner join tblProviderName pn on pn.PNNameFk = n.NameGuid '
				set @sql = @sql + ' inner join (select top 1 v.pk '
				set @sql = @sql + '   from #vals v where v.val is not null '
				set @sql = @sql + '   group by v.val, v.pk ' 
				set @sql = @sql + '   order by count(*) desc) f on f.Pk = pn.PNPk '
				set @sql = @sql + ' where NameGuid = ''' + cast(@nameGuid as varchar(38)) + ''''
				
			end 
			else
			begin		
				set @sql = 'insert #top2(val, number) '
				set @sql = @sql + 'select top 2 val, count(*) from #vals where val is not null '
				set @sql = @sql + ' group by val order by count(*) desc '
				
				exec(@sql)
				
				set @first = null
				set @second = null
				select @first = number from #top2 where counter = 1
				select @second = number from #top2 where counter = 2
				
				if (@second is null or @first > @second or @source = 'PNNameCanonical') set @sql ='update n set ' + @dest + ' = f.' + @source + ', NameUpdatedDate = getdate(), NameUpdatedBy = ''' + @user + ''' '
				else set @sql = 'update n set ' + @dest + ' = null, NameUpdatedDate = getdate(), NameUpdatedBy = ''' + @user + ''' '
				
				set @sql = @sql + ' from tblName n '
				set @sql = @sql + ' inner join (select top 1 ' + @source + ', count(*) as c, PNNameFk '
				set @sql = @sql + '      from tblProviderName '
				set @sql = @sql + '      where PNNameFk = ''' + cast(@nameGuid as varchar(38)) + ''' '
				set @sql = @sql + '      group by ' + @source + ', PNNameFk '
				set @sql = @sql + '      order by c desc) f on f.PNNameFk = n.NameGuid '
				set @sql = @sql + ' where n.NameGuid = ''' + cast(@nameGuid as varchar(38)) + ''''
			end
			    		
			drop table #top2		
		end
		
		print(@type)
		print(@sql)
		exec(@sql)
		
		set @row = @row + 1
	end
	
	--update parent and preferred fks
	declare @parentFk uniqueidentifier
	--try system PN rec
	select top 1 @parentfk = p.PNNameFk 
	from vwProviderName pn
	inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
	inner join vwProviderName p on p.PNNameId = pc.PCName2Id and p.ProviderPk = pc.ProviderPk
	where pn.PNNameFk = @nameGuid and p.ProviderIsEditor = 1 and pc.PCRelationshipFk = 6 --parent
	
	if (@parentFk is null)
	begin
		--try parentage PN rec
		select top 1 @parentfk = p.PNNameFk 
		from vwProviderName pn
		inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
		inner join vwProviderName p on p.PNNameId = pc.PCName2Id and p.ProviderPk = pc.ProviderPk
		inner join tblProvider pr on pr.ProviderPk = p.ProviderPk
		where pn.PNNameFk = @nameGuid and pr.ProviderUseForParentage = 1 and pc.PCRelationshipFk = 6 --parent	
	end
	
	if (@parentFk is null)
	begin
		--use first
		select top 1 @parentfk = p.PNNameFk 
		from vwProviderName pn
		inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
		inner join vwProviderName p on p.PNNameId = pc.PCName2Id and p.ProviderPk = pc.ProviderPk
		where pn.PNNameFk = @nameGuid and pc.PCRelationshipFk = 6 --parent	
	end

	--only update if not null, othewise it is probably currently pointing to the 'unknown' node
	if (@parentFk is not null)
	begin
		update n
		set n.NameParentFk = @parentFk, n.NameParent = pn.NameFull
		from tblName n
		inner join tblName pn on pn.NameGuid = @parentFk
		where n.NameGuid = @nameGuid				
	end
	
	
	declare @prefFk uniqueidentifier
	--try system PN rec
	select top 1 @prefFk = p.PNNameFk 
	from vwProviderName pn
	inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
	inner join vwProviderName p on p.PNNameId = pc.PCName2Id and p.ProviderPk = pc.ProviderPk
	where pn.PNNameFk = @nameGuid and p.ProviderIsEditor = 1 and pc.PCRelationshipFk = 15 --preferred
		
	if (@prefFk is null)
	begin
		--use first
		select top 1 @prefFk = p.PNNameFk 
		from vwProviderName pn
		inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
		inner join vwProviderName p on p.PNNameId = pc.PCName2Id and p.ProviderPk = pc.ProviderPk
		where pn.PNNameFk = @nameGuid and pc.PCRelationshipFk = 15 --preferred	
	end

	update n
	set n.NamePreferredFk = @prefFk, n.NamePreferred = pn.NameFull
	from tblName n
	inner join tblName pn on pn.NameGuid = @prefFk
	where n.NameGuid = @nameGuid		


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_NameRank
	@nameGuid uniqueidentifier,
	@user nvarchar(50)
AS

	--update NameRankFk to point to rank in tblRank table (ONLY if currently null)
	-- match on any of the known ranks for each rank
	
	declare  @nRank nvarchar(100), @curRankFk int
	
	select @nRank = lower(NameRank), @curRankFk = NameRankFk
	from tblName 
	where NameGuid = @nameGuid
	
	if (@curRankFk is not null) 
	begin
		select @curRankFk
		return
	end
	
	declare @ranks table(Counter int identity, RankPk int, KnownRanks nvarchar(500))
		
	
	insert into @ranks
	select RankPk, '@' + RankKnownAbbreviations + '@'
	from tblRank
		
	declare @pos int, @count int, @abbrevs nvarchar(500), @pk int, @setRank int
	select @pos = 1, @count = count(*) from @ranks
	
	while (@pos < @count + 1)
	begin
		select @abbrevs = KnownRanks, @pk = RankPk from @ranks where Counter = @pos
		
		if (charindex('@' + @nRank + '@', @abbrevs) <> 0)
		begin			
			update tblName 
			set NameRankFk = @Pk, NameUpdatedDate = getdate(), NameUpdatedBy = @user
			where NameGuid = @nameGuid
			
			set @setRank = @Pk
			set @count = -1 --end loop			
		end
		
		set @pos = @pos + 1
	end
	
	select @setRank


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_NameRelationships
	@nameGuid uniqueidentifier,
	@providerNamePk int,
	@user nvarchar(50)
AS
	--sets the name relationships for a name based on the provided providername.  
	--update concepts to point to correct name guid

	declare @nameId nvarchar(300)
	declare @refFk uniqueidentifier
	declare @typeFk uniqueidentifier
	declare @basFk uniqueidentifier
	declare @basedFk uniqueidentifier
	declare @consFk uniqueidentifier
	declare @homoFk uniqueidentifier
	declare @replFk uniqueidentifier
	declare @parentFk uniqueidentifier
	
	--todo PNReferenceFk ?
	
	--TypeName, PNTypeNameId
	select @typeFk = n.PNNameFk 
	from vwProviderName pn
	inner join vwProviderName n on n.PNNameId = pn.PNTypeNameId and n.ProviderPk = pn.ProviderPk
	where pn.PNPk = @providerNamePk
	
	--Basionym, PNBasionymId
	select @basFk = n.PNNameFk 
	from vwProviderName pn
	inner join vwProviderName n on n.PNNameId = pn.PNBasionymId and n.ProviderPk = pn.ProviderPk
	where pn.PNPk = @providerNamePk
	
	--BasedOn, PNBasedOnId
	select @basedFk = n.PNNameFk 
	from vwProviderName pn
	inner join vwProviderName n on n.PNNameId = pn.PNBasedOnId and n.ProviderPk = pn.ProviderPk
	where pn.PNPk = @providerNamePk
	
	--ConservedAgainst, PNConservedAgainstId
	select @consFk = n.PNNameFk 
	from vwProviderName pn
	inner join vwProviderName n on n.PNNameId = pn.PNConservedAgainstId and n.ProviderPk = pn.ProviderPk
	where pn.PNPk = @providerNamePk
	
	--HomonymOf, PNHomonymOfId
	select @homoFk = n.PNNameFk 
	from vwProviderName pn
	inner join vwProviderName n on n.PNNameId = pn.PNHomonymOfId and n.ProviderPk = pn.ProviderPk
	where pn.PNPk = @providerNamePk
	
	--ReplacementFor, PNReplacementForId
	select @replFk = n.PNNameFk 
	from vwProviderName pn
	inner join vwProviderName n on n.PNNameId = pn.PNReplacementForId and n.ProviderPk = pn.ProviderPk
	where pn.PNPk = @providerNamePk
	
	--parent
	select @parentFk = p.PNNameFk
	from vwProviderName pn
	inner join vwProviderConcept pc on pc.PCName1Id = pn.PNNameId and pc.ProviderPk = pn.ProviderPk
	inner join vwProviderName p on p.PNNameId = pc.PCName2Id
	where pn.PNPk = @providerNamePk and pc.PCRelationshipFk = 6 
	
	--update name
	
	update tblName
	set NameReferenceFk = @refFk,
		NameTypeNameFk = @typeFk,
		NameBasionymFk = @basFk,
		NameBasedOnFk = @basedFk, 
		NameConservedAgainstFk = @consFk,
		NameHomonymOfFk = @homoFk,
		NameReplacementForFk = @replFk,
		NameParentFk = isnull(@parentFk, NameParentFk)
	where NameGuid = @nameGuid


	--update concepts
		
	update c
	set ConceptName1Fk = @nameGuid,
		ConceptUpdatedBy = @user,
		ConceptUpdatedDate = getdate()
	from tblConcept c
	inner join vwProviderConcept pc on pc.PCConceptFk = c.ConceptPk
	inner join vwProviderName pn on pn.PNNameId = pc.PCName1Id and pn.ProviderPk = pc.ProviderPk
	where pn.PNPk = @providerNamePk
	
	update c
	set ConceptName2Fk = @nameGuid,
		ConceptUpdatedBy = @user,
		ConceptUpdatedDate = getdate()
	from tblConcept c
	inner join vwProviderConcept pc on pc.PCConceptFk = c.ConceptPk
	inner join vwProviderName pn on pn.PNNameId = pc.PCName2Id and pn.ProviderPk = pc.ProviderPk
	where pn.PNPk = @providerNamePk
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ProviderConceptRelationship
	@PCPk int,
	@user nvarchar(50)
AS
	--update ProviderConceptRelationshipFks to point to relationship in tblReltationshipType table
	
	exec sprInsert_ProviderConceptChange @pcpk, @user
	
	declare @pcRel nvarchar(100), @curRelFk int, @setRel int
					
	select @pcRel = lower(PCRelationship), @curRelFk = PCRelationshipFk
	from tblProviderConcept
	where PCPk = @PCPk
	
	if (@curRelFk is null) 
	begin				
		declare @rels table(Counter int identity, RelPk int, Rel nvarchar(300), Inverse nvarchar(300))
					
		insert into @rels
		select RelationshipTypePk, RelationshipTypeName, RelationshipTypeInverse
		from tblRelationshipType
			
		
		declare @pos int, @count int, @rel nvarchar(300), @inverse nvarchar(300), @pk int
		select @pos = 1, @count = count(*) from @rels
		
		while (@pos < @count + 1)
		begin
			select @rel = Rel, @inverse = Inverse, @pk = RelPk from @rels where Counter = @pos
			
			if (@pcRel = @rel)
			begin
				update tblProviderConcept
				set PCRelationshipFk = @Pk, PCUpdatedDate = getdate(), PCUpdatedBy = @user
				where PCPk = @pcpk
				
				set @setRel = @Pk
				set @count = -1 --end loop			
			end
			
			if (@pcRel = @inverse) --inverse relationship - swap name ids
			begin
				declare @name1ID nvarchar(300), @name2ID nvarchar(300)
				select @name1ID = PCName1Id, @name2ID = PCName2Id from tblProviderConcept where PCPk = @pcpk
				
				update tblProviderConcept
				set PCName1Id = @name2ID, 
					PCName2Id = @name1ID,
					PCRelationshipFk = @Pk,
					PCUpdatedDate = getdate(), 
					PCUpdatedBy = @user
				where PCPk = @pcpk
				
				set @setRel = @Pk
				set @count = -1 --end loop	
			end
				
			set @pos = @pos + 1
		end
	end		
	else
	begin
		set @setRel = @curRelFk
	end
		
	select @setRel
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ProviderConceptStatus
	@PCPk int,
	@Status nvarchar(20),
	@user nvarchar(50)
AS

	exec sprInsert_ProviderConceptChange @pcpk, @user
	
	update tblProviderConcept
	set PCLinkStatus = @status, PCUpdatedBy = @user, PCUpdatedDate = getdate()
	where PCPk = @PCPk


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ProviderNameConcepts
	@PNPk int,
	@user nvarchar(50)
AS

	--for each provider concept for the provider name, link to existing concept based on name pk, rel type, and according to
	--otherwise insert a new concept if one doesnt exist
	-- this MUST be run after ProviderNames have been linked to Names and ProviderReferences have been linked to References
	--OBSELETE
	
	exec sprInsert_ProviderNameChange @pnpk, @user
	
	declare @name1Fk uniqueidentifier, @name2Fk uniqueidentifier, @nameId nvarchar(300), @provPk int
	select @name1Fk = PNNameFk, @nameId = PNNameId, @provPk = ProviderPk
	from vwProviderName 
	where PNPk = @PNPk
	
	declare @concepts table(Counter int identity, PCPk int, Name2Id nvarchar(300), RelFk int, AccToId nvarchar(300))	
	insert into @concepts
	select PCPk, PCName2Id, PCRelationshipFk, PCAccordingToId
	from vwProviderConceptRelationship
	where PCName1Id = @nameId and ProviderPk = @provPk
	
	declare @concPos int, @concLen int, @pcpk int, @name2Id nvarchar(300), @pcRel int, @accToId nvarchar(4000)
	declare @cPk int, @accToFk uniqueidentifier
	
	select @concPos = 1, @concLen = count(*) from @concepts
		
	while (@concPos < @concLen + 1)
	begin

		select @pcpk = PCPk, @name2Id = Name2Id, @pcRel = RelFk, @accToId = AccToId
		from @concepts
		where Counter = @concPos
		
		select @name2Fk = PNNameFk from vwProviderName where PNNameId = @name2Id and ProviderPk = @provPk
		select @accToFk = PRReferenceFk from vwProviderReference where PRReferenceId = @accToId and ProviderPk = @provPk
		
		if (@name2Fk is not null and @pcRel is not null)
		begin
			select @cPk = ConceptPk 
			from tblConcept
			where ConceptName1Fk = @name1Fk and ConceptName2Fk = @name2Fk 
				and ConceptRelationshipFk = @pcRel and 
				(@accToFk is null or ConceptAccordingToFk = @accToFk)
		
			if (@cPk is not null)
			begin
				update tblProviderConcept
				set PCConceptFk = @cPk,
					PCLinkStatus = 'Matched',
					PCUpdatedDate = getdate(),
					PCUpdatedBy = @user
				where PCPk = @pcpk			
			end
			else
			begin
				--insert new concept
				insert tblConcept
				select PCName1, @name1Fk, PCName2, @name2Fk, PCRelationship, @pcRel, 
					PCAccordingTo, @accToFk, PCHybridOrder, getdate(), @user, null, null
				from tblProviderConcept
				where PCPk = @pcpk
				
				select @cPk = @@identity
				update tblProviderConcept
				set PCConceptFk = @cPk,
					PCLinkStatus = 'Matched',
					PCUpdatedDate = getdate(),
					PCUpdatedBy = @user
				where PCPk = @pcpk				
			end
		end
		
		set @concPos = @concPos + 1
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ProviderNameRank
	@providerNamePk int,
	@user nvarchar(50)
AS

	--update ProviderNameRankFk to point to rank in tblRank table
	-- match on any of the known ranks for each rank
	
	exec sprInsert_ProviderNameChange @providerNamePk, @user
	
	declare  @pnRank nvarchar(100), @curRankFk int
	
	select @pnRank = lower(PNNameRank), @curRankFk = PNNameRankFk
	from tblProviderName 
	where PNPk = @providerNamePk
	
	if (@curRankFk is not null) 
	begin
		select @curRankFk
		return
	end
	
	declare @ranks table(Counter int identity, RankPk int, KnownRanks nvarchar(500))
		
	
	insert into @ranks
	select RankPk, '@' + RankKnownAbbreviations + '@'
	from tblRank
		
	declare @pos int, @count int, @abbrevs nvarchar(500), @pk int, @setRank int
	select @pos = 1, @count = count(*) from @ranks
	
	while (@pos < @count + 1)
	begin
		select @abbrevs = KnownRanks, @pk = RankPk from @ranks where Counter = @pos
		
		if (charindex('@' + @pnRank + '@', @abbrevs) <> 0)
		begin
			update tblProviderName 
			set PNNameRankFk = @Pk, PNUpdatedDate = getdate(), PNUpdatedBy = @user
			where PNPk = @providerNamePk
			
			set @setRank = @Pk
			set @count = -1 --end loop			
		end
		
		set @pos = @pos + 1
	end
	
	select @setRank
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ProviderNameRelationships
	@providerNamePk int,
	@user nvarchar(50)
AS

	exec sprInsert_ProviderNameChange @providerNamepk, @user
	
	--update relationships to point to namePks for associated nameIds
	--must be done after integration so all nameFks have been be set

	declare @refFk uniqueidentifier
	declare @typeFk uniqueidentifier
	declare @basFk uniqueidentifier
	declare @basedFk uniqueidentifier
	declare @consFk uniqueidentifier
	declare @homoFk uniqueidentifier
	declare @replFk uniqueidentifier
	
	--todo PNReferenceFk ?
	
	--TypeName, PNTypeNameId
	select @typeFk = n.PNNameFk 
	from tblProviderName pn
	inner join tblProviderName n on n.PNNameId = pn.PNTypeNameId
	where pn.PNPk = @providerNamePk
	
	--Basionym, PNBasionymId
	select @basFk = n.PNNameFk 
	from tblProviderName pn
	inner join tblProviderName n on n.PNNameId = pn.PNBasionymId
	where pn.PNPk = @providerNamePk
	
	--BasedOn, PNBasedOnId
	select @basedFk = n.PNNameFk 
	from tblProviderName pn
	inner join tblProviderName n on n.PNNameId = pn.PNBasedOnId
	where pn.PNPk = @providerNamePk
	
	--ConservedAgainst, PNConservedAgainstId
	select @consFk = n.PNNameFk 
	from tblProviderName pn
	inner join tblProviderName n on n.PNNameId = pn.PNConservedAgainstId
	where pn.PNPk = @providerNamePk
	
	--HomonymOf, PNHomonymOfId
	select @homoFk = n.PNNameFk 
	from tblProviderName pn
	inner join tblProviderName n on n.PNNameId = pn.PNHomonymOfId
	where pn.PNPk = @providerNamePk
	
	--ReplacementFor, PNReplacementForId
	select @replFk = n.PNNameFk 
	from tblProviderName pn
	inner join tblProviderName n on n.PNNameId = pn.PNReplacementForId
	where pn.PNPk = @providerNamePk
	
	update tblProviderName
	set PNTypeNameFk = @typeFk,
		PNBasionymFk = @basFk,
		PNBasedOnFk = @basedFk,
		PNConservedAgainstFk = @consFk,
		PNHomonymOfFk = @homoFk,
		PNReplacementForFk = @replFk,
		PNUpdatedDate = getdate(),
		PNUpdatedBy = @user
	where PNPk = @providerNamePk
	
	
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_Reference
	@referenceGuid uniqueidentifier,
	@referenceLSID nvarchar(300),
	@referenceCitation nvarchar(4000),
	@referenceFullCitation ntext,
	@user nvarchar(50)
AS

	update tblReference
	set ReferenceLSID = @referenceLSID,
		ReferenceCitation = @referenceCitation,
		ReferenceFullCitation = @referenceFullCitation,
		ReferenceUpdatedDate = getdate(),
		ReferenceUpdatedBy = @user
		

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure sprUpdate_ReferenceData
	@refGuid uniqueidentifier,
	@user nvarchar(50)	
AS

	--update RIS data from provider RIS	
	--get source records 
	--if a compositae/system record exists then use any non-null fields to populate RIS data
	--otherwise populate from majority value for each field
	--if equal number of majority values, eg 2 of each value, then update to null

	declare @cols table(row int identity, source nvarchar(100), dest nvarchar(100))
	
	insert @cols
	select RISMappingSourceCol, RISMappingDestCol
	from tblRISMapping
	
	declare @row int, @count int, @sql nvarchar(1000), @source nvarchar(100), @dest nvarchar(100), @type sysname
	select @row = 1, @count = count(*) from @cols
	
	declare @first int, @second int
	create table #vals(val nvarchar(4000), pk int, isSystem bit)
					
	while (@row < @count + 1)
	begin		
		select @source = source, @dest = dest from @cols where row = @row
		
		select @type = t.name 
		from systypes t 
		inner join syscolumns c on c.xtype = t.xtype and t.status = c.typestat
		inner join sysobjects o on o.id = c.id
		where c.name = @source and o.name = 'tblProviderRIS'
		
		delete #vals
		set @sql = 'insert #vals select cast(' + @source + ' as nvarchar(4000)), PRISPk, isnull(ProviderIsEditor,0) '
		set @sql = @sql + ' from vwProviderRIS ris '
		set @sql = @sql + ' inner join tblProviderReference pr on pr.PRPk = ris.PRISProviderReferenceFk '
		set @sql = @sql + ' where pr.PRReferenceFk = ''' + cast(@refGuid as varchar(38)) + ''' and ' + @source + ' is not null'			
		set @sql = @sql + ' and pr.PRLinkStatus <> ''Discarded'''
		exec(@sql)
				
		if (exists(select * from #vals where isSystem = 1))
		begin
			--checklist system entered value
			if (@type = 'ntext')
			begin			
				--special case for text type fields
				set @sql = 'update ris set RISUpdatedDate = getdate(), RISUpdatedBy = ''' + @user + ''', '
				set @sql = @sql + @dest + ' = pr.' + @source
				set @sql = @sql + ' from tblReferenceRIS ris '
				set @sql = @sql + ' inner join vwProviderRIS pr on pr.PRReferenceFk = ris.RISReferenceFk '
				set @sql = @sql + ' inner join (select top 1 v.pk '
				set @sql = @sql + '   from #vals v where v.val is not null and v.isSystem = 1 '
				set @sql = @sql + '   group by v.pk ' 
				set @sql = @sql + '   order by count(*) desc) f on f.Pk = pr.PRISPk '
				set @sql = @sql + ' where RISReferenceFk = ''' + cast(@refGuid as varchar(38)) + ''''
			end 
			else
			begin
				set @sql = 'update tblReferenceRIS set ' + @dest + ' = (select top 1 pr.' + @source 
				set @sql = @sql + ' from vwProviderRIS pr inner join #vals v on v.pk = pr.PRISPk where v.isSystem = 1) '
				set @sql = @sql + ', RISUpdatedBy = ''' + @user + ''', RISUpdatedDate = getdate() '
				set @sql = @sql + ' where RISReferenceFk = ''' + cast(@refGuid as varchar(38)) + ''''
			end
		end
		else
		begin
			create table #top2(counter int identity(1,1), val nvarchar(4000), number int)
			
			--find most common value
			if (@type = 'ntext')
			begin			
				--special case for text type fields
				set @sql = 'insert into #top2(val, number) '
				set @sql = @sql + 'select top 2 val, count(*) from #vals where val is not null '
				set @sql = @sql + ' group by val order by count(*) desc '
				
				exec(@sql)
				
				set @first = null
				set @second = null				
				select @first = number from #top2 where counter = 1
				select @second = number from #top2 where counter = 2
				
				if (@second is null or @first > @second) set @sql ='update ris set ' + @dest + ' = pr.' + @source + ', RISUpdatedDate = getdate(), RISUpdatedBy = ''' + @user + ''' '
				else set @sql = 'update ris set ' + @dest + ' = null, RISUpdatedDate = getdate(), RISUpdatedBy = ''' + @user + ''' '
				
				set @sql = @sql + ' from tblReferenceRIS ris '
				set @sql = @sql + ' inner join vwProviderRIS pr on pr.PRReferenceFk = ris.RISReferenceFk '
				set @sql = @sql + ' inner join (select top 1 v.pk '
				set @sql = @sql + '   from #vals v where v.val is not null '
				set @sql = @sql + '   group by v.val, v.pk ' 
				set @sql = @sql + '   order by count(*) desc) f on f.Pk = pr.PRISPk '
				set @sql = @sql + ' where RISReferenceFk = ''' + cast(@refGuid as varchar(38)) + ''''
				
			end 
			else
			begin		
				set @sql = 'insert #top2(val, number) '
				set @sql = @sql + 'select top 2 val, count(*) from #vals where val is not null '
				set @sql = @sql + ' group by val order by count(*) desc '
				
				exec(@sql)
				
				set @first = null
				set @second = null
				select @first = number from #top2 where counter = 1
				select @second = number from #top2 where counter = 2
				
				if (@second is null or @first > @second) set @sql ='update ris set ' + @dest + ' = f.' + @source + ', RISUpdatedDate = getdate(), RISUpdatedBy = ''' + @user + ''' '
				else set @sql = 'update ris set ' + @dest + ' = null, RISUpdatedDate = getdate(), RISUpdatedBy = ''' + @user + ''' '
				
				set @sql = @sql + ' from tblReferenceRIS ris '
				set @sql = @sql + ' inner join (select top 1 ' + @source + ', count(*) as c, PRReferenceFk '
				set @sql = @sql + '      from vwProviderRIS '
				set @sql = @sql + '      where PRReferenceFk = ''' + cast(@refGuid as varchar(38)) + ''' '
				set @sql = @sql + '      group by ' + @source + ', PRReferenceFk '
				set @sql = @sql + '      order by c desc) f on f.PRReferenceFk = ris.RISReferenceFk '
				set @sql = @sql + ' where ris.RISReferenceFk = ''' + cast(@refGuid as varchar(38)) + ''''
			end
			    		
			drop table #top2		
		end
		
		print(@type)
		print(@sql)
		exec(@sql)
		
		set @row = @row + 1
	end
	
	
	--update reference citation from RIS
	--todo other types of citation
	declare @cit nvarchar(4000)
	select @cit = isnull(RISAuthors,'') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + ' ' + isnull(RISAuthors2,'') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + cast(RISTitle as nvarchar(2000)) + '.','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISVolume + '.','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + cast(RISTitle2 as nvarchar(2000)),'') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISIssue + '.','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISCityOfPublication + ',','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISPublisher + '.','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISStartPage + ' -','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISEndPage + '.','') from tblReferenceRIS where RISReferenceFk = @refGuid
	select @cit = @cit + isnull(' ' + RISWebUrl + '.','') from tblReferenceRIS where RISReferenceFk = @refGuid

	update tblReference set ReferenceCitation = @cit where ReferenceGuid = @refGuid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

